We are currently developing a online advert site for people to buy and sell (similar to gumtree) difference being this will be used for employees who work for the company, it wont be reachable from people outside the company.
Now we have 15 categories which have sub categories and those sub categories have child categories.
We have a main table called Adverts which consists on ItemId, Title, SubTitle, Description, CreatedBy, BroughtBy, StartDate, EndDate and ParentCategoryId, SubCategoryId and ChildCategoryId etc
Now instead of having one massive tables which consists of all the details for the item they are selling we were going to create separate table(s) per category for the details of the item.
So we would have Advert.Vehicle_Spec which would have all the details about a car they were selling i.e ItemId (which will be a FK to the main Advert table), Make, Model, Colour, Mot, Tax etc
That way when we query the main table Advert we can join onto the relevant Spec table which in a way would keep the tables clean and tidy now my question would be to you is this a good approach? Would there be any performance issues with this approach? I will create all the relevant FK where needed to help with queries etc.
I did ask this question on an SQL Server forum and one individual suggested using XML - each category gets an XML schema and the XML tags and values are held in a single field but the data varies depending on what type of item is being sold. This requires more setup but probably has the best overall balance of performance and flexibility, I Personally have never worked with XML within SQL so I can't comment on this being a good approach or not?
Each category can have many different status's we have a variety of tables already which hold the description of each status, the queries we will be performing will vary from select, delete, insert, update some queries will have multiple joins on to the Status/User table, we will also be implementing a "Suggested" form which will show all records suggested for a user depending on what they search for.
Is XML right for this in regards to flexibility and performance?
XML seems to be a good approach for this, you can directly write stored procedures that queries the specific categories you want and organize them into tables and display them. You will then possibly want to use something like XSLT to extract the XML data and display them in a table.