Database table design question

[ittechguy]

I have a database I'm building which is in 3rd normal form. After adding real data to the tables and doing lots of testing, I wonder if I've broken it apart too far and perhaps have 1 or 2 too many tables.

The relationships look like this:



Specifically, for tblCustomer I have 4 separate lookup tables, each with unique records in them. Those are tblRank, tblOrganization, tblShopName, and tblOfficeSym. I am wondering if I should consolidate tblShopName and tblOfficeSym into one table, or if its good to keep them separate?

An Organization is like a Company. Within that organization are shops or workcenters. For an example, within Civil Engineering organization there can be the Plumbing shop, the Electric Shop, etc. A shop almost always has an office symbol which is a 5 letter code. However there are many shops which currently do not have shop names, but have office symbols. The reason for this is simply because I do not currently know what the shop name is, but I intend to find out that information. Given the extremely large number of shops, this is going to take some time.

Additionally, although rare, some shops do not have office symbols. Not that I don't know the office symbol, but that they simply don't have one. In which case, I repeat the shop name in tblOfficeSym to make it easier for users who don't know any better. For an example, we have a gas station. The company is the company of the gas station, the shop name is the name of the gas station because technically the gas station is a workcenter. And then I repeat that in tblOfficeSym because I figure the user might be stupid and might search in one or the other field, and I want to make it easy for them to find what they're looking for.

Now, I have a couple different search forms which contain cascading combo boxes. A user selects Organization, and then Shop Name and Office Symbol combo boxes are filtered based on tblCustomer.OrganizationFK. As you know, this only works if there is an existing record for a customer with that Organization and Shop Name/Office Symbol in tblCustomer. So if adding a customer who has a new shop name / office symbol, that shop name / office symbol wont show up in the combo boxes. However, this problem is resolved by adding a OrganizationFK field in both tblShopName and tblOfficeSym and linking them to tblOrganization.OrganizationPK then filter the combo boxes by tblOrganization instead of tblCustomer.




All that said, I am not sure whether I should delete tblOfficeSym and add OfficeSym as a field to tblShopName then add OrganizationFK to that table and spend some time copying data over to the new location, or if I should leave it as is and add OrganizationFK to both tables.

[John_G]

For the parts of the structure you are talking about, I don't see much wrong with it.

Since any given shop has only one symbol associated with it, then you could (and probably should) combine tblShopName and tblOfficeSym into one.

The way you have it, a customer is in one organization, and has only one shop/symbol. There is no explicit relationship between shop and organization, so you can't put OrganizationFK into ShopName. If there IS a relationship (i.e. shops ARE relatiod to organizations), then your diagram isn't right - but how to fix it will depend on exactly what your relationships are.


Can you provide a bit more detail on how your relationships work?

[ittechguy]

Thanks for your reply.

I ended up creating a relationship between tblShopName and tblOrganization. Also between. TblOfficeSym and tblOrganization. I think I'll leave it like that for now until I get around to merging shop name and officesym. I agree they should be in one table, but it seems to work with them separate. I suppose how it is now there is less null values for if I don't know the shop name. I'll try to explain the relationships tomorrow.