I am revisiting an old file discussed and have the following question which will probably move me in the right direction.
I have a Contact table and the contact type can be Customer, Supplier or Prospect
Whilst a lot of the basic information is the same for all of them if they are a supplier (for example) they will have a Supplier account number which would not apply to any other contact type and there is prob also other info ONLY needed for a supplier
The same applies to Customers and Prospects
As I see it I have three obvious options
- If contact is a Prospect I want to store information about their existing Copy Volume and copy costs plus any proposed copy costs and maybe also lease details.
I could store all these “attributes” (have been looking at Training Series) – in separate tables there would be quite a lot of them. Eg Lease details, Proposal details, etc etc.
- I could have Supplier Attributes – Prospect Attributes etc each in a different table as the content would be different. Currently they are all in the contacts table but there is a different layout for each contact type so only see the relevant information but it means that I have a lot of empty/redundant fields with no data.
- Another possibility is to have a Supplier table with all fields relevant to suppliers
A Prospect Table with all fields relevant to prospects and a Customer Table with all relevant details to Customers.
Which would you recommend and why please.