Appropriate way to link one table to multiple others?

[superwormy]

What I want is a 'correspondence' table which might be linked to several other tables.

For instance, I have these three tables:

[seller]
ID, fname, lname, num_items_sold

[buyer]
ID, fname, lname, billing_address

[user]
ID, username, fname, lname, password

What I'd like is a generic correspondance table so that I can record any correspondence I have with any of these types of people, and have these people linked to their correspondance records.

[correspondance]
ID, subject, message, start_date

I could add seller_ID, buyer_ID, and user_ID fields to the correspondence table, but it seems awefully messy... Can anyone think of a better way to do this?

[ffsja]

superwormy,

It sounds as though you'll be having multiple one-to-many relationships, if I'm reading your post right. Each seller,buyer, or user can have many correspondences, but each correspondence can only have one buyer, user, or seller, correct?

It really would be best to put foreign keys in the correspondence table that matches up with the primary keys in each of the other three tables. It's sorta messy I guess but at least it normalizes your DB. I can't think of a better way to do it, but doing it this way is not so bad (in my opinion). 🙂

S./

[superwormy]

You are correct in what you said... thats the only way I can think of to do it as well... maybe I'll go that route.

Any other ideas?