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?
- Should i create a customer_type field as well inside invoices in order to distinguish invoices of customers with same id but different type?
- Should i include a prefix in each customer.id inside invoices/customers in order to distinguish invoices of customers of different type?
- Should i make a separate invoices table one for each customer type?
- 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!