| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2011-10-14 : 16:02:47
|
| I stumbled upon what I thought was a weird scenario relating to Normalization.For the Customer, the business requirment is that sometimes a Sub Sector is applicable, sometimes only the Sector is applicable.Here are the Tables:Customers----------ID PK...SectorCode - FK to Sectors.CodeSubSectorCode - FK to SubSectors.Code...Sectors-------Code PKDescription...SubSectors----------Code PKSectorCode - FK to Sectors.CodeDescription...The issue is in the Customer Table. When there is a SubSector we put both the Sector and the SubSector. Does this violate normalization? How best to handle, when the SubSector is relevant, we could put the SubSector only - then the Sector field will sometimes be null and sometimes not null. The Sector will be not null in cases where the Sector only is relevant. Or is this making too big a deal of the normalization issue? |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-10-14 : 19:48:31
|
| IMO, since the SubSector table is dependent on the Sector table, putting the SubSector key in the Customer table does break normalization. Your query would probably run faster normalized. Also, how would your present schema react when multiple SubSectors are involved?=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-10-18 : 10:45:10
|
quote:
Originally posted by Bustaz Kool
IMO, since the SubSector table is dependent on the Sector table, putting the SubSector key in the Customer table does break normalization. Your query would probably run faster normalized. Also, how would your present schema react when multiple SubSectors are involved?
Thankyou.I am not worried if putting SubSector in the Customer table breaks normalization. I am wondering, when the Customer is associated to a SubSector, would having a value for both the Sector AND the SubSector violate normalization? But I need the Sector there since sometimes a Customer is related to a Sector only (i.e. no SubSector).Good point about the multiple Sub Sectors. But in this particular System, there is only one. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2011-10-18 : 14:18:25
|
Why does sectors hold any data besides just descriptions?All sector links if I am understanding you correctly should be in SUBSECTORS (but I would name the table differently since it is no longer just subsectors).Just designate by a field if something is a SUBSECTOR.(It is possible I am not understanding the need properly, but based on my understanding)Customers----------ID PK...SectorCode - FK to Sectors.CodeSubSectorCode - FK to SubSectors.Code...Sectors_info-------Code PKDescription...Sectors_Details----------Code PKSectorCode - FK to Sectors.CodeSubSector BIT/integer
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-10-18 : 14:31:41
|
quote:
Originally posted by Vinnie881
Why does sectors hold any data besides just descriptions?All sector links if I am understanding you correctly should be in SUBSECTORS (but I would name the table differently since it is no longer just subsectors).<snip>
Based on the description, I would assume that Sector - SubSector is like a hierarchy. Just like United States is a Country (Sector) and Texas is a State (SubSector). |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2011-10-18 : 15:27:18
|
I see your sample as a different type of hierarchy (I may be wrong on my interpretation though). That scenario you illustrated I would still design differently, and again I would not require a need for two detail tables to house the records, only a separate table for the descriptors.COUNTRY(countryID,countrydesc,...)State(stateid,statedesc,...)MYDETAIL(Mydetailid,stateid,countryid,...)But the scenario denis_the_thief posted above, I am interpreting differently than you, and see it as even if it's a hierarchy as you illustrated, there is still no reason to hold the detail record in two tables, since it is the same record type with just simply a need of a way to classify the data as a sub. You can design the hierarchy using a parentid column or whatever depending on needs, but I am not seeing a need for two tables to hold details in this scenerio since it appears to be the same record type.Again, this is purely based on my interpretation of his issue, and I may very well have interpreted incorrectly, but I see no need for two separate detail tables that house the same information with just a different classification.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-10-18 : 18:12:50
|
| Yes, SubSector/Sector is a heirarchy, Sector being the parent.To use the State/Country analogy would be like worldwide, sometimes the customer is located in a state like Texas other times the business is dealing with a small country, they just put the country. So when the State is relevant, would putting the Country also in the Company Record violate normalization? We need that column there (I think) in cases where the business enters a country and no state. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-10-18 : 18:14:12
|
quote:
Originally posted by Vinnie881
Why does sectors hold any data besides just descriptions?
I put '...' for the other columns so I could get straight to the point. I sometimes find it hard when posters put so much info that has low-relevance. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2011-10-18 : 21:29:52
|
OK, I think I am following you. So a Sector and a Sub-Sector are different things and not just the same thing but set to a different level of a hierarchy (i.e. a sub-sector for one item will never be a sector). In that case your design works. Sometimes a 100% normalized database is not always possible in every situations, and that doesn't mean when it's not possible that it's a bad design. However, if you want to provide some sample data of your issue, it might help people figure out if this design best suits your data though.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
|
Normalization Question - Sub Sector