I am trying to construct a Database for a client.
He has 3 tables, Customers, Clients, Suppliers.
Now with his current database in the Customer Table he has Account Numbers in field Names.
Customers (customerNumber, AutoNumber)
Orders (orderNumber, AutoNumber)
Suppliers (supplierNumber, AutoNumber)
Customers to Orders (via customerNumber)
Suppliers to Orders (via supplierNumber)
For Example one fieldname is IBM_account_num, another is MICROSOFT_account_num. We want to design it so those fields dont have to be hard coded.
If he were to open an account with DELL, the only way to update the DB is add another field. There must be another way.
I am thinking adding a 4th table and calling it Accounts and relating the Customer and Suppliers tables through it simular to the way Orders was.
Is this kind of logic ok, or am I heading in the wrong direction?
Accounts (accountId, AutoNumber)
Customers to Accounts (via customerNumber)
Suppliers to Accounts (via supplierNumber)
Orders to Accounts (via orderNumber)