Table structure/relation

[s@s]

Hi all, first (theoretical) post for a noob user, so bear with me

So I got a number of different customer types. They share some common fields, but they also got their own separate set of fields. so i thought i will make one table for each customer type (2-3 so far) instead of one big table.

now, all customers, regardless of type, also got invoices with one of the invoice fields ofc being customer_id, i am thinking of making one invoice table common for all customer types.

what would be the best practice to link invoices.customer_id with the several customer tables?

1. Should i create a customer_type field as well inside invoices in order to distinguish invoices of customers with same id but different type?
2. Should i include a prefix in each customer.id inside invoices/customers in order to distinguish invoices of customers of different type?
3. Should i make a separate invoices table one for each customer type?
4. any other approach?

case 3 is clear i think but not that elegant: customersA.id=autonumber and invoicesA.customer_id=number and their relation is straightforward.

how would i table-relate invoices with customers in the other approaches above?

any feedback will be appreciated, thx!

[CJ_London]

They share some common fields, but they also got their own separate set of fields. so i thought i will make one table for each customer type (2-3 so far) instead of one big table.

Without knowing your business and rules then 'as a rule' better to have one table and include a customer type field. Depends what you mean by separate set of fields but you can leave the ones that belong to another type blank (empty fields take up no space) or perhaps some can be multi purpose. Only issue would be if you were going to exceed 255 fields across all customer types

1. see above
2. no - the link back to customer does that
3. definitely no
4. see above

[ArviLaanemets]

In addition to Ajax response, about type-related customer info:
1. If this info is unique for every customer, then go with fields in customers table. You may set the visibility of controls in form(s) linked to those fields true or false, depending on customer type;
2. If for every customer type is certain value for every of those type-related parameters, then you can have a separate table, where those parameters for every type are set;
3. If customer type can have some set of values value for at least some of those type-related parameters, then you need an additional field for subtypes, and a table, where parameters for every subtype of every type are set;

For 2. and 3., type-related info about customer is read from type parameters table.