0

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?

Code Ratchet
  • 5,758
  • 18
  • 77
  • 141

1 Answers1

1

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.

hysoftwareeng
  • 497
  • 4
  • 15
  • 2
    The only consideration with this approach is that you might need to think carefully about foreign key constraints and integrity. I don't think that you can enforce foreign key constraints within XML. – Greg the Incredulous Dec 02 '14 at 04:37
  • Scott gave ItemID as a common column for example, then he can just use Inner Joins to query the sub-tables on the conditions they are equal, the problem is more with using XML PATH/XML AUTO, etc and later on displaying the data in a table using XSLT. – hysoftwareeng Dec 02 '14 at 04:48
  • @LebronJames sorry for the late reply, above seems to be the right approach, for storing this information in the DB how could I structure it? i.e datatype etc should I extend the Adverts table by one column that would hold this XML value? I did find this but im not sure if this is what your talking about http://msdn.microsoft.com/en-us/library/ms176009.aspx – Code Ratchet Dec 15 '14 at 09:22