November 28, 2014 at 8:05 pm
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.
Thanks for your time.
November 30, 2014 at 2:47 pm
You have three basic choices, none of which are particularly great 🙂
1) the option you have outlined with a different child table for each 'type' of advert. Maintenance is a PITA because every time you add a new category, you need a new child table and new interface so you have to change the back end database and the front end code.
2) EAV - Entity, Attribute, Value tables. These store each attribute (colour, mileage, regitration, ISBN, pedigree, vintage etc..) in one record per attribute with N records for each item being sold. Front end developers like them because you get a consistent interface and they are probably quite good for your purposes where old records are no longer relevant, but are a nightmare for efficient drill down and aggregation reporting.
3) 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 flexibilty.
November 30, 2014 at 11:51 pm
Quick thought, you might want to look at sparse columns as the fourth alternative in addition to the options
aaron.reese mentioned, recent discussion on the subject on this thread
😎
Edit: fixed the link
December 1, 2014 at 2:58 am
The link you provided keeps coming up as bad request?
Regarding option three though how would performance be when we have more then 50k records in the DB do you have an example of doing option three within the db?
December 1, 2014 at 3:07 am
.Netter (12/1/2014)
The link you provided keeps coming up as bad request?
Fixed the link, sorry about that;-)
😎
December 1, 2014 at 4:29 am
50K is not a big record set. What sort of queries are you going to be doing? I can't imagine that you want to do much analytics on this data; probably more interested in serving up the currently active adverts to a buyer and recently active adverts to the advertiser. You would have indexes on the relevant fields and tables for user and category which means that number of records actually being scanned in depth would be much less than the 50K.
SQL's XML parser is pretty efficient, but I would expect that most of the parsing would be done on the front end application.
December 1, 2014 at 5:16 am
It will be a variety of different queries ranging from select, update, delete, inner joins to relevant tables to get descriptions, displaying any adverts to user that are recommended depending on there activity on the site, showing new items on the home page which is pretty much all status driven I was just using 50k as an example it could potentially be a lot more then that so what I implement now has to be sufficient for later on down the line and growth, also each advert with have its own primary key which is pretty standard nowadays
Main things I'm focusing on is performance, and keeping the database neat and tidy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply