I've been running in circles with the design of my database for about a month now.
It's a database to store the details of all our company's contacts. Different fields apply to different groups. We need to know different peices of information about potential donors, staff, lawyers, suppliers etcetera.
Because of that I should probably store the differnet types of contact in separate tables with distinct fields.
BUT.... I need to be able to amalgamate records from any of these tables, so there can't be any duplicate primary key values between the tables. But even if I enforce referential integrity with cascade updating and deletion, the autonumbers of separate tables don't co-ordinate with each other; and if I turn off auto-number I leave users to guess at safe primary key values, which is no good, I don't even want them to see the primary key values anyway.
I thought about having a simple All_Contacts table (just containing name fields, and maybe other universally applicable fields), joined in a many to many relationship with a Relevance_to_us table, as one contact might fall under more than one category of relevance. But then I still need tables storing the additional information fields associated with each category of relevance, and I fall back into the problem of not being able to co-ordinate the auto-number primary key fields.
About a week ago I decided there was no problem just having one contact table with ALL the fields relevant to any contact. And that the many blank fields didn't matter since the data is only ever accessed via forms, and I can divide up the contacts and select out the appropriate fields at the point of form creation. This all seemed fine and dandy, until I started reading more articles on data normalisation on friday night. According to what I've read that model clearly violates the rules of normalisation. I still haven't fully understood how it would lead to deletion, update and insertion anomolies...
But anyway, any help?
It's a database to store the details of all our company's contacts. Different fields apply to different groups. We need to know different peices of information about potential donors, staff, lawyers, suppliers etcetera.
Because of that I should probably store the differnet types of contact in separate tables with distinct fields.
BUT.... I need to be able to amalgamate records from any of these tables, so there can't be any duplicate primary key values between the tables. But even if I enforce referential integrity with cascade updating and deletion, the autonumbers of separate tables don't co-ordinate with each other; and if I turn off auto-number I leave users to guess at safe primary key values, which is no good, I don't even want them to see the primary key values anyway.
I thought about having a simple All_Contacts table (just containing name fields, and maybe other universally applicable fields), joined in a many to many relationship with a Relevance_to_us table, as one contact might fall under more than one category of relevance. But then I still need tables storing the additional information fields associated with each category of relevance, and I fall back into the problem of not being able to co-ordinate the auto-number primary key fields.
About a week ago I decided there was no problem just having one contact table with ALL the fields relevant to any contact. And that the many blank fields didn't matter since the data is only ever accessed via forms, and I can divide up the contacts and select out the appropriate fields at the point of form creation. This all seemed fine and dandy, until I started reading more articles on data normalisation on friday night. According to what I've read that model clearly violates the rules of normalisation. I still haven't fully understood how it would lead to deletion, update and insertion anomolies...
But anyway, any help?
