Design method

[Craig6]

Hello, Is it prudent to have two tables when building a customer database, one for contacts and one for company names.

I have a customer table which is based on company names and has fields for contacts. However, there may be many contact names for each company. So I'm thinking I should create 2 tables,one where my contacts live and the form would then have a combobox for the company name linked to the customer table.

Or, is it best to stick with one table (as i've got) and have a few more fields showing contact1, contact2 etc

Thanks

 

[Craig6]

Yes, my customer table will have many contacts in the contact table and will be related. I've now created a contacts table and linked it to the customer table. Thanks

 

[Deleted member 147267]

You should consider this as pretty much a truth to go by: If you need to add another field to allow for the same kind of data, your design approach is flawed. It's what I call using your Excel brain. Not only can that make data retrieval difficult, it means having to add that field to every existing query, form, report and perhaps code as well. That's another sign of what's known as a lack of normalization.

 

[CJ_London]

you may need more tables still - perhaps the contact has more than one telephone number? perhaps the customer has more than one address?

 

[Craig6]

I can see this growing and growing then. I suppose a contact will have more than one number but I could just create fields in the table called, Phone, Mobile, Home etc?

 

[AccessBlaster]

Craig,

The good news is you do not have to reinvent the wheel, you can look at existing Customer table designs and adapt it to your needs.

Not being an expert database builder, I have always relied on the older Microsoft Northwind database sample as my go to reference.
In the sample below, you can use the order details or the customer details or a combination. Make it simple or complex, its up to you.

HTH, good luck with your project.

 

[RogerCooper]

Hello, Is it prudent to have two tables when building a customer database, one for contacts and one for company names.

I have a customer table which is based on company names and has fields for contacts. However, there may be many contact names for each company. So I'm thinking I should create 2 tables,one where my contacts live and the form would then have a combobox for the company name linked to the customer table.

Thanks

Make sure to set up a 1-to-many relationship between customers and contacts. We have an ERP here that treats customers and contacts as having a many-to-many relationship, which makes it much harder to work with. Although it is possible to have the same contact at more than 1 customer, it is better to treat those exceptional situations as multiple contacts.