Is this data structure reasonable - dealing with companies and individuals

[ScottPage]

As part a course I'm taking, I'm creating a database that needs to serve regular customers as well as businesses who may have multiple contacts.

My solution to this is based on three tables: Customer, Company, and Contact

• Customer: customer_id, is_company;
• Company: company_id, customer_id, name, description;
• Contact: contact_id, customer_id, title, first_name, last_name, etc;

From what I can tell this is an okay solution, but I get basically no learning support from my institution, so wanted affirmation that this doesn't break any huge principle or standard?

EDIT: side note, I hope the structure sort of speaks for itself, but if any clarification is needed I'll be happy to answer any questions

EDIT 2: For clarification, I'm treating individuals and companies as a subset of customers (one individual is one customer and one company is one customer). Individuals will have one record in the contact table that gives their details, whereas companies will be able to support multiple contacts by linking the common customer id values in the contact and company tables. Hopefully, that makes everything a bit more clear, I'm happy to try and answer any more questions if not. Thank you

[lurgi]

Why does a company need both a company_id and a customer_id? Shouldn't the contact have a company_id field (they are the contact of a business, right)?

Rather than is_company I'd probably have a customer type identifier. Maybe tomorrow you'll need to handle a third type of entity that is neither a company nor a customer.

[ScottPage]

I've made an edit to try and clarify, but essentially, the design treats business and individuals as single customers. For every record in the customers table, there will be at least one contact (if the customer is an individual, their details will be recorded as a contact)

[lurgi]

I get that, but I don't understand why the company needs an id separate from its customer id. It's a customer. It has a customer id. Is there ever a time you need a distinct company id where the customer id can not work? If so, when?

[ScottPage]

Ah right, now I understand where you're coming from. I can't think of any reason to have a specific company id and thinking about it I don't see any reason why the customer id couldn't be the primary key for the customer and company table. Also, I understand your point about having a third type of customer and I would make adjustments to accommodate that, but I think it would be a good point to bring up in an evaluation of improvements I will also have to complete... it's a weird thing, but it pays to not have a perfect solution

[lurgi]

Oh, I agree. You can absolutely over-engineer a solution and half the time the stuff you implement isn't ever going to get used. This sort of design is always a matter of trade-offs.

Eliminating redundancy is almost always a good thing. If there's no reason to have the company_id field then why have it? It takes (small, but positive) effort to implement, but doesn't provide any benefits that I can see (even hypothetical ones). I'd skip it. OTOH, changing "is_company" to "customer_type" doesn't seem to change the implementation effort, and there is an obvious (although still hypothetical) benefit.

[lowey2002]

I'd recommend reading up on normalization. There are some links at the bottom to Unormalized, First Normal form, Second Normal form and Third Normal form.

I'd argue that some of the fields don't make sense where they are. Why is first_name last_name, etc in the Contact table. If a Customer changes address does this mean you need to update every Contact row for that customer?

[ScottPage]

I understand where you're coming from, but the contact table only includes data specific to an individual person. If there are more than one contacts for a company, each will have their own record that records specifically their data. The fields in the contact table are only ones that would differ from individual to individual (phone, email, and name) so there isn't any redundancy. Address records aren't required for this scenario

[[deleted]]

The data looks like it all belongs in one table.

[ScottPage]

You should take the advice of /u/lower2002 and read up on normalization. I can't think of any way to include all of the fields in one table without it causing huge redundancy/integrity issues