What I am after here is "best practice".

I understand that I can have:
tbl_person:
Person_ID (Primary Key, autoincrement)
other columns

tbl_supplier
Supplier_ID (Primary Key, autoincrement)
Person_ID (Index)
other columns

tbl_address
Address_ID (Primary Key, autoincrement)
Person_ID (Index)
Supplier_ID (Index)
other columns

Here we have a table of persons (who have addresses) and a table of suppliers (who can have people working for them who have addresses and the supplier themselves have an address) and a table of addresses (people addresses and supplier addresses).

What I am trying to get away from is having to add a new column to tbl_address every time I have a new relationship.

One way is to have tables of keys:
tbl_person:
Person_ID (Primary Key, autoincrement)
other columns

tbl_supplier
Supplier_ID (Primary Key, autoincrement)
Person_ID (Index)
other columns

tbl_address
Address_ID (Primary Key, autoincrement)
Supplier_ID (Index)
other columns

tbl_person_address_keys
PAK_ID (Primary Key, autoincrement)
Person_ID (Index)
Address_ID (Index)

tbl_supplier_address_keys
SAK_ID (Primary Key, autoincrement)
Supplier_ID (Index)
Address_ID (Index)

tbl_supplier_person_keys
SPK_ID (Primary Key, autoincrement)
Supplier_ID (Index)
Person_ID (Index)

Then when ever I want to find a specific relationship it is a simple matter of joining three tables (i.e., tbl_person, tbl_address and tbl_person_address_keys).

The tricky part is knowing what the Primary Keys are when you INSERT into the table as they are created for you automatically (autoincrement) and are therefore effectively hidden from you at the time of insert.

This was where "$PersonID=mysql_insert_id() " comes in. From reading the MySQL manual it only returned the value of the Primary Key to the row you just INSERTED in the current session. Therefore, it will not return the Primary Key of the row created by someone else, in the split second between you INSERTING and calling "$PersonID=mysql_insert_id() ", in another session.

Or is there a better way to do this?

Your views and suggestions are all greatly appreciated,

Glyn

I think you're getting the direction of the relationship the wrong way round. Why not have:

Table: People
PersonID (PK, AutoIncrement)
AddressID (FK - Addresses.AddressID)
PersonName
PersonTel
etc...

Table: Suppliers
SupplierID (PK, AutoIncrement)
AddressID (FK - Addresses.AddressID)
SupplierName
etc...

Table: Addresses
AddressID (PK, AutoIncrement)
AddressLine1
PostCode
etc...

The link tables you have used, I only use that sort of thing in many-many relationships, such as where a person may belong to more than one group, and a group may have more than one person in it.
If your 'People' are only ever going to be employees of 'Suppliers', then put a column called 'SupplierID' within 'People'. If not, then this is another situation where a link table might come in handy.

I'm not sure entirely what you mean with the mysql_insert_id issue. I use MS SQL myself, and I just use something like:

INSERT INTO Table(Col1, Col2) VALUES (@Value1, @Value2); SELECT @NewID = @@IDENTITY;

then I just get the @NewID parameter value for the PK of the record inserted.

Hi Minaki,

That makes sence and is another way to do it. However, then I have to have a key filed for each relationship in the tbl_person (i.e., address, e-mail address, telephone number, etc).

tbl_person
Person_ID (PK, autoincrement)
Address_ID (FK - Address.Address_ID)
Email_ID (FK - Email.Email_ID)
Telephone_ID (FK - Telephone.Telephone.ID)

And I still have the problem that if a new relationship is required I need to add a new field to tbl_person.

Now, what about multiple relationships? A person can have a home address, business address, home telephone number, business telephone number, mobile telephone number, hone facimile number, business facimile number, etc.

I suppose I would need to have a FK for each of these?

Any recomendations (I am enjoying this, I hope you are - or is it my warped sence of humour).

Regards,

Glyn

Do you really need to store things like E-Mail Addresses and phone numbers in a seperate table? People aren't usually going to have more than about 3 telephone numbers, or e-mail addresses, at least not that you need to know about... what most people do is just have 3 telephone fields (Tel1, Tel2, Tel3 or HomeTel, OfficeTel, MobileTel)

I agree with Minaki have seperate tables for all those fields is carrying normalisation a little far. But, with a bit of lateral thinking you could easily use one table with 5 fields;

Contact_details;
ID: autonumber[PK] as the record id
person_ID [FK]
data_type: text T|F|E for tel No, fax No or email
data_loc: text H|B for home or business
data: text to contain the phone number or email details

this will give unlimited entries for each person

I guess it's just a question of finding the balance between how flexible you want your database to be verses how much admin work you're willing to put into it (both now and later)

Hi Minaki and chrishirst,

You are both correct. Depending on what you want to achieve.

First to clarify. I never intended to use a differnt table for each type of telephone number, just a separate table for telephone numbers and addresses. One table for telephone numbers can obviously be used to cater for most tasks and the design sugested by chrishirst is a very good one. However, personally, I would not put all the telephone numbers in one row as suggested, I think unless I missunderstand, by Minaki. The same applies to address.

I am doing this for self interest. So I think this time I will try the extream version with tbl_key_etc, for now. However, I can see that having the relationship stored within the tables may be prefereable for some applications. Minaki hits the nail right on the head when she says "I guess it's just a question of finding the balance between how flexible you want your database to be verses how much admin work you're willing to put into it (both now and later)".

I hope you have all enjoyed this line of though as much as I have (told you I had a warped sence of humour), as well as those others who have read this thread.

Regards,

Glyn