A Case for Ignoring Normalized Data (2 Viewers)

[Thales750]

Years ago, a friend of mine was building a SQL database in a time before most people had even heard of SQL. Early to mid 80s or so.
He maintained that a list of companies (table) should include all companies you do business with and should include all organizations that are stake holders.

This would put customers and vendors in the same table. At first glance this seems like a simple little question, but it has implications that will effect overall design for years to come. I'm going to reserve putting in my two cents at this point just to see where this goes.

Thank you all for participating in what could become a fairly complex conversation.

 

[Minty]

I'm currently dealing with a database (inherited) that has effectively two tables of addresses related to two different types of clients.
The nightmare this causes when trying to anything vaguely clever is beyond belief.

Either type of client can be in the main data as a source address, or a supply address, and there are flags in each main record to indicate which is which.

So to get an address out you have to jump through a massive array of hoops that are on fire and spinning at the same time just to get a postcode.
I would never, ever, in a million years have designed it like this, and am sorely tempted to redo things, but its been in development for over 20 years so the hoop jumping is inbuilt absolutely everywhere.

 

[MajP]

You could do that if customer and vendor info is nearly the same fields. Name, address, poc etc. But normally you have a lot of different fields so you would have to have a lot of fields not filled out. Then enforcing rules for required data gets complex.

 

[Pat Hartman]

The people who create separate tables for each entity do so because each entity is used in a different context. They don't ever consider the possibility that a given entity might actually occur in multiple roles. A vendor may also be a customer. A teacher might also be a student. A student might also be a parent, etc. Then they get into designing and building the application and they find themselves creating other virtually identical tables for each entity such as contacts, addresses, notes, communications, etc. And then they get cute and try to make common forms for each of the entity's child tables or even worse, try to combine the child tables for all entities so that the single address table contains addresses for customers, vendors, employees, etc which is really a nightmare.

If your business truly has little to no overlap with entities appearing in multiple roles, then I have no objection to the separate but equal table design pattern -- BUT, all the child tables MUST also be separate. Then, when the data elements and validation rules are identical, it is OK to reuse forms. If the parent tables are separate, under no conditions should the child tables be combined. You give up the ability to use RI which is too important to lose. When you reuse subforms, you need to not fill in the master/child links because the PK/FK names will be different for each pairing. That means that you need to populate the FK manually as you do for popup forms.

However, having worked with both types of schemas over the years, using an entity schema where all entities are in a single table and there are combined child tables so there is only one address table, one contacts table, etc. is easier in the long run. In some applications, some of the entities will have a sufficient number of ancillary attributes that are used only for that entity type that you might make an additional child table rather than include these fields in the entity table. Usually, we're only talking about a few fields and so I don't bother. Sometimes, I make a field multi-use so LastName and CompanyName are always required but mutually exclusive so I name the column LastOrCompany since the validation rule would be required but not usually anything else. So, in my applications, the entity table might be slightly denormalized in that it includes columns that are only required for specific entity types and columns that are multi-use depending on the entity.

 

[cheekybuddha]

This would put customers and vendors in the same table.

Are you saying this is normalised or not normalised?

To me, this is normalised.

I would then have specific customer and vendor tables with properties (fields) specific to each and with a foreign key field to link to the companies table.

 

[gemma-the-husky]

Personally, I don't put customers and supplies in the same table.

I think it's probably somewhat unusual for a "contact entity" to be both customer and supplier, and I think it's as easy to keep the two separate. If an entity gets duplicated, it's not the end of the world, and I see a customer entity as different to a supplier entity, so it's not really a duplication. Even if you deal with 2 parts of the same company in practice the contacts, addresses, phone numbers, email addresses may will be different in each case.

I don't think this is strictly a normalisation issue.

 

[cheekybuddha]

For me, a company is a company.

If you have the same company in two different places and you need to update address or number etc, then you have to make sure it's done twice

 

[Thales750]

The people who create separate tables for each entity do so because each entity is used in a different context. They don't ever consider the possibility that a given entity might actually occur in multiple roles. A vendor may also be a customer. A teacher might also be a student. A student might also be a parent, etc. Then they get into designing and building the application and they find themselves creating other virtually identical tables for each entity such as contacts, addresses, notes, communications, etc. And then they get cute and try to make common forms for each of the entity's child tables or even worse, try to combine the child tables for all entities so that the single address table contains addresses for customers, vendors, employees, etc which is really a nightmare.

If your business truly has little to no overlap with entities appearing in multiple roles, then I have no objection to the separate but equal table design pattern -- BUT, all the child tables MUST also be separate. Then, when the data elements and validation rules are identical, it is OK to reuse forms. If the parent tables are separate, under no conditions should the child tables be combined. You give up the ability to use RI which is too important to lose. When you reuse subforms, you need to not fill in the master/child links because the PK/FK names will be different for each pairing. That means that you need to populate the FK manually as you do for popup forms.

However, having worked with both types of schemas over the years, using an entity schema where all entities are in a single table and there are combined child tables so there is only one address table, one contacts table, etc. is easier in the long run. In some applications, some of the entities will have a sufficient number of ancillary attributes that are used only for that entity type that you might make an additional child table rather than include these fields in the entity table. Usually, we're only talking about a few fields and so I don't bother. Sometimes, I make a field multi-use so LastName and CompanyName are always required but mutually exclusive so I name the column LastOrCompany since the validation rule would be required but not usually anything else. So, in my applications, the entity table might be slightly denormalized in that it includes columns that are only required for specific entity types and columns that are multi-use depending on the entity.

I agree 100%. I build systems for builder/developers and chemical plants. In both cases, customers and vendors are often the same folks. They are differentiated with things like Vendor Agreements and Vendor Agreement Details.
It still gets a little tricky if you are associating contact's to other contacts. I've been using universal many to many table for this but it does require extra development to make it work.

 

[Thales750]

Customers are usually associated with buying stuff from you and therefore associated with inventory reductions. Vendors are ususally associated with selling you stuff and associated with inventory additions. It's probably best to keep them in separate tables. Inventory reductions contain a customer foreign key and inventory additions contain a vendor foreign key. If you do keep them in the same table then you would need an option group indicator so each record can be idenified as either customer or vendor. You would still have the same inventory issues though.

Both of those should be handled in agreements and orders tables. Even Purchase Orders and Sales Orders should be stored in the same table. One with a "-" and the other with a "+".
I have come to the point where I think that anytime data looks the same it should be stored in the same place. Differences in the data should be stored in sub-tables.

 

[Thales750]

I'm currently dealing with a database (inherited) that has effectively two tables of addresses related to two different types of clients.
The nightmare this causes when trying to anything vaguely clever is beyond belief.

Either type of client can be in the main data as a source address, or a supply address, and there are flags in each main record to indicate which is which.

So to get an address out you have to jump through a massive array of hoops that are on fire and spinning at the same time just to get a postcode.
I would never, ever, in a million years have designed it like this, and am sorely tempted to redo things, but its been in development for over 20 years so the hoop jumping is inbuilt absolutely everywhere.

I'm interpreting this as combine whenever possible and err towards more, rather than less. Is that correct?

 

[Thales750]

You could do that if customer and vendor info is nearly the same fields. Name, address, poc etc. But normally you have a lot of different fields so you would have to have a lot of fields not filled out. Then enforcing rules for required data gets complex.

I agree, the data does get complex. I'm handling that with Vendor Agreements and Customer Agreements. In construction and in the chemical industry we have many customers that are also vendors. In addition to this, many folks change jobs within their industries. It's nice to be able to keep track of their careers.

 

[Thales750]

Are you saying this is normalised or not normalised?

To me, this is normalised.

I would then have specific customer and vendor tables with properties (fields) specific to each and with a foreign key field to link to the companies table.

I agree, that is normalized, the wording was to suggest that folks could examine their definition of normalized.

 

[Thales750]

Personally, I don't put customers and supplies in the same table.

I think it's probably somewhat unusual for a "contact entity" to be both customer and supplier, and I think it's as easy to keep the two separate. If an entity gets duplicated, it's not the end of the world, and I see a customer entity as different to a supplier entity, so it's not really a duplication. Even if you deal with 2 parts of the same company in practice the contacts, addresses, phone numbers, email addresses may will be different in each case.

I don't think this is strictly a normalisation issue.

I'm actually disagreeing with you on this. Especially in situation like Pat described. Over all I think the real world is much better described with many to many relationships than it is, in many cases, by many to one. Originally I seprated Vendors from Customers knowing that they could be both and would be. I paid a price of having too many "many to many" relationships and the need for Union Queries. I like Union Queries, when I can't get take out, meaning as a last resort.

 

[Pat Hartman]

I'm not sure why you would have to union vendors and customers. The records would be very unlikely to be used in the same context unless you were doing something like sending a letter/email to all entities to inform them of an address change for your company. Otherwise, you would be selecting only vendors to populate combos when you are buying something or customers when you are selling something. Since an entity can be used for both things, their UsedFor or Type or Role or whatever you call the table that indicates a use for an entity, can have multiple rows which would make them show up in multiple select lists.

When you use a single entity table, you might have to make some of the child tables, such as contact, dependent on the Junction table between Entity and Role because you probably don't have the same contacts for purchases as you do for sales. But you could, so that relationship becomes m-m also. But m-m with the junction table, NOT the entity table because except in specific types of applications, you won't have contacts that are m-m with entities.

The pro for using an entity table is that you have better control over contacts and addresses and won't have to worry about having to change them in multiple places. You get to have one set of child tables and one set of forms, etc so your objects are greatly reduced as well as the redundant coding. The querydefs are also reduced because the queries take Role arguments when you only want to select entities that are used for a specific role.

 

[LarryE]

I'm not sure why you would have to union vendors and customers. The records would be very unlikely to be used in the same context unless you were doing something like sending a letter/email to all entities to inform them of an address change for your company. Otherwise, you would be selecting only vendors to populate combos when you are buying something or customers when you are selling something. Since an entity can be used for both things, their UsedFor or Type or Role or whatever you call the table that indicates a use for an entity, can have multiple rows which would make them show up in multiple select lists.

When you use a single entity table, you might have to make some of the child tables, such as contact, dependent on the Junction table between Entity and Role because you probably don't have the same contacts for purchases as you do for sales. But you could, so that relationship becomes m-m also. But m-m with the junction table, NOT the entity table because except in specific types of applications, you won't have contacts that are m-m with entities.

The pro for using an entity table is that you have better control over contacts and addresses and won't have to worry about having to change them in multiple places. You get to have one set of child tables and one set of forms, etc so your objects are greatly reduced as well as the redundant coding. The querydefs are also reduced because the queries take Role arguments when you only want to select entities that are used for a specific role.

Pat:
Could you do something like this?

I've never done it, I suppose you could.

 

[Pat Hartman]

No. This is EXACTLY what I said to not do. If you keep Sales and Purchase Orders in separate tables, you MUST keep all the related tables separate also. It is wrong to have both SalesOrderID and PurchaseOrderID in the inventory table. And your contacts are almost certainly not m-m. The contacts for the purchasing dept will be different from the Sales department.

BTW, Inventory is separate from Products The product table defines an Item. But the Inventory table defines how many you have on hand and each item will be a transaction which is a sale, an order, or an inventory adjustment.

 

[Thales750]

I'm not sure why you would have to union vendors and customers.

When doing searches by phone number, contact, or Address, which is fairly common, maybe a project manager or purchasing agent remembers contact info but not where they work, etc.
I use the M-M tables I refer to them as Universal Links They have dual foreign keys on one side a single foreign on the other . One key indicates the table the other the record. Here is the layout. The phone used to have an additional Link Table, but it was so much work to maintain that it's gone. No one cares if there are an infinite number of records with the same phone number. That's probably the same with the address, a problem could be if the company moves all of the contacts' address would need to change. Or the form would link back to the company and display there address on that contact form. So a union query would be needed.

For simplification the Phone and Address links from the Main Entities table on the left have been omitted. Also omitted are the "*Table" links, which is represented by the tblCompaniesProfile link to the tbl LinkUniversalContacts sample

 

[Thales750]

This would be an alternate to the above. In this scenario a single table would contain entities with relationships to the company. At the next level these entities would be combined to create links to the contacts through tblLinkUniversalContacts. Both of these methods described require union queries to reverse the search source. Meanin if you need to find a project or a vendor from a contact a Union query would help.

tblSource and tblActionItems is a holdover from a system we adopted years ago. In spite of the fact that the system ended up being completely redeveloped, the names stuck. tbleSource is actually Projects and tbleActionItems is a Task List.

 

[Thales750]

This is a form that adds new Contacts. It key word searches for existing Contacts and if a match is found the User can select to Link the current (in this case Customer) entity to the contact or create another one.

 

[mike60smart]

This would be an alternate to the above. In this scenario a single table would contain entities with relationships to the company. At the next level these entities would be combined to create links to the contacts through tblLinkUniversalContacts. Both of these methods described require union queries to reverse the search source. Meanin if you need to find a project or a vendor from a contact a Union query would help.

tblSource and tblActionItems is a holdover from a system we adopted years ago. In spite of the fact that the system ended up being completely redeveloped, the names stuck. tbleSource is actually Projects and tbleActionItems is a Task List.


View attachment 111146

I would suggest that you Enforce Referential Integrity between tables.