Best practice for multiple 0,1 to many relationships

Question

I'm an application developer without a ton of database design experience and I'm wondering if someone can suggest a design/implementation for relating one table to one or more other tables.

For example, if I have Account, Policy and Claim tables, each may have 0 or more Contacts.

I could define multiple foreign key fields in the Contacts table, but there must be a better way.

i.e.

varchar(50) contact_name
varchar(100) contact_address
bigint Account_ID
bigint Policy_ID
bigint Claim_ID

Answer 1

In this instance, your statement of the relationship pretty much determines the design.

Each account may have zero, one or many contacts.

This is implemented with an intersection table.

create table AccountsContacts(
    AccountID     int not null,
    ContactID     int not null,
    constraint PK_AccountsContacts( AccountID, ContactID ),
    constraint FK_AccountsContacts_Account foreign key( AccountID )
        references Accounts( ID ),
    constraint FK_AccountsContacts_Contact foreign key( ContactID )
        references Contacts( ID )
);

The same process is used with policies and claims.

Answer 2

This will really depend on many factors, beginning with your choice of RDBMS, and followed by your application language and framework. The choice will be very different if you use some form of RAILS and mysql (where database design is driven by class design in your application), than PHP and PostgreSQL (where you can choose to use create a CONTACT composite type, and keep contacts as an array of contacts in a column on each of your databases, without a separate table for it).