Mailing Database Help

[toby]

So, I have a business mailing list all in one table - full address, primary contact, phone no, email, etc. Auto number as a primary key (Organisation ID). It has a combo box for each record to distingish the industry sector i.e. University & Colleges, Council, Charity, etc.

Because we deal with various people within each company, I have a 2nd table Called Secondary Contacts. Auto number as primary key (Contact ID). Organisation ID. Then name, position, phone no., email, etc.

I have a one-to-many relationship between Organisation ID & Contact ID. So in Access 2010 when adding a new address to the 1st table, I can just click on the "+" and add secondary contacts (effectively straight into the 2nd table) which link to the address in the 1st.

So - its now getting bigger - so I've spilt the 1st table into seperate tables for each industry sector with the intention of forming one-to-many relationship between each table and secondary contacts. (Then getting rid of the original 1st table).

But as I've already got so far down the line - I've had to keep Organisation ID 'as is' for each seperate table. So when I form the relationship between Organisation ID (in the new table) and Contact ID (in existing Secondary Contact table) it is all as it was originally.

What I am concious of is... having deleted and added records many times in the 1st table I created - the primary key (Organisation ID) became non-sequential & now I've split the 1st table - say into 8 different tables. I have a primary key in each of the seperate tables within each starting (& flowing) in random numbers. One table may have records with primary keys which go 5,25,329. Then another 1,8,47,254.



I had considered splitting the 1st table without Organisation ID. B

But since I've gone so far down the line and have a decent sized Secondary Contacts table (with no means other than xref Organisation ID in, to identifiy which primary contact/address in relates). I would then have to manually re-input all of secondary contacts into a new table (as I had done originally).

I hope this makes sense!!... So my question is:-

1) Is there a better way to do this, in terms of design? (I want to start as a mean to go on, so I don't have this problem in future)
2) If I just leave it with the random primary keys, can you foresee any issues?

[toby]

Sorry my mistake - Third paragraph - I meant - I have a one-to-many relationship between Organisation ID (1st table) & Organisation ID (2nd table - secondary contacts)

[toby]

sorry I also meant - when I make the new relationship between the new 8 tables & secondary contacts - I'll add further compound keys to Secondary Contacts - so like 1st table Organisation ID, 2nd table Organisation ID, 3rd table Organisation ID, etc.

[ItsMe]

I am not sure what the question is. I don't know what concern it is to have non-sequential PK's. The autonumber field will manage newly created records/rows. All you have to worry about is not importing duplicate values when migrating data from one table to another (new) table.

As for the compound key.... The way I understand Compound keys is that you actually combine fields that will create a single Compound Key field. So you might take the value of the Autonumber PK field in a table and combine another field (like a candidate key) from the same table and combine the two.

Perhaps you can view your tables as having many Foreign Key fields where some Foreign Key fields are duplicate data (from other table's Key fields). This may be an easier way to approach it, rather than creating compound keys. With this approach, you can use Composite keys and your key fields will be more dynamic because you can query up or down the relations. A composite key may only be good to query to the parent or the child where choosing from a couple/few foreign keys that store duplicate data provide an option to include parent or child.

WHERE the PK field and Composite Key field = the Composite Key field and Composite key field of another table.