June 10, 2008 at 3:12 pm
Is it a good idea to have a single table or multiple table to represent a face of an entity
Eg.
One owner can have one or more organization. Each organization have one or more vendors. All the 3 entities will have same attributes.
You have 2 ways of implementations.
1. Have 3 tables - Owner, Organization, Vendor. All 3 tables will have same columns.
2. Have just one table, Organization - which will have Owner,Org and vendor data. Entity relationship table which stores the relationship between them.
First one looks straigt forward but all the 3 entities have same attributes and all the 3 entities connect to all other other tables seperately like u have owner user table, org user table, vendor user table, owner feature table, org feature table, vendor feature table etc. So i have 3 times of all tables. This kind of implementation involves tooo many tables and relationships which will be difficult to maintain. And also insert or select might involve too many tables or joins.
Second one seems pretty neat and straight forward. Easy to implement and maintain. It might seem that it is not very flexible comapared to first one but when you come to flexibility, I guess both has the same level of flexibility. Right now, i dont see any disadvantage so i vote for this. What do guys think?
What do you guys vote for and why?
But
Any thoughts is appreciated.
Thanks for your time,
Kayal
June 11, 2008 at 6:38 am
From what you described, I'd go with a single table, Organization, to describe the basic attributes of that object. Then, I'd add a table, let's call it OrganizationType, that lists the various types of Organizations and then another table that maps Organizations to OrganizationTypes, because it's pretty likely that an Org can be both a Vendor and a Consumer, depending on perspective. Then of course, you'll also need a Relationship table to relate one Organization to another, and a Relationship Type table to determine the information about the relationship... Ain't this fun?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 11, 2008 at 10:17 am
Grant,
Yep, i I appreciate your view on this. Thanks 🙂
Kayal
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply