I’ve been making my systems more and more normalized. My filter for something occurring more than once is infinity. So the question for normalizing is will it ever happen and can there ever be two.
I am now storing all companies (whether vendor, customer or any other) in one list.
In other words there is no longer a “tblVendors” or a “tblCustomers”, there are now Vendor Agreements and Customer Agreements that each reference the mother table “tblCompanies”.
Likewise all contacts in one list. And in both cases all of them have daughter tables for any data that can ever change,
So the table tblContacts can only have “FirstName”, “LastName”, “DateOfBirth”, and if needed SS#, that’s it. Everything else is in daughter tables.
There are a lot of problems with this.
1. Every time you are going to add a new customer or a new vendor you must look both the tblCompanies and the tblContacts to make sure those names don’t already exist and if they do, are they duplicates etc. etc.. The list goes on and on…
2. You must find a way to limit queries to one phone number, one email, one address, etc. etc.
I can’t tell you how much extra work this has caused and how many unforeseen data management issues; and users finding ways to do things you would have not dreamed possible.
So my new answer is wizards; for any data entry that involves new accounts or new contacts or something involved.
I am now storing all companies (whether vendor, customer or any other) in one list.
In other words there is no longer a “tblVendors” or a “tblCustomers”, there are now Vendor Agreements and Customer Agreements that each reference the mother table “tblCompanies”.
Likewise all contacts in one list. And in both cases all of them have daughter tables for any data that can ever change,
So the table tblContacts can only have “FirstName”, “LastName”, “DateOfBirth”, and if needed SS#, that’s it. Everything else is in daughter tables.
There are a lot of problems with this.
1. Every time you are going to add a new customer or a new vendor you must look both the tblCompanies and the tblContacts to make sure those names don’t already exist and if they do, are they duplicates etc. etc.. The list goes on and on…
2. You must find a way to limit queries to one phone number, one email, one address, etc. etc.
I can’t tell you how much extra work this has caused and how many unforeseen data management issues; and users finding ways to do things you would have not dreamed possible.
So my new answer is wizards; for any data entry that involves new accounts or new contacts or something involved.
Last edited: