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.