CODEWITHSUNDEEP
mysqldatabase-designmany-to-many
Amit Erandole
Amit Erandole

Reputation: 12281

How do I design a contacts database and store multiple line items per user?

I want to create a small invoicing application. My first step is creating a database with two tables where each client can have multiple addresses and phone numbers.

I think its safe to assume here that I am dealing with a one to many relationship. But how should I design the tables and fields so they can store multiple addresses and phone numbers per user? By multiple I also mean indeterminate since so I can't just have x no of columns in my table. Off Hand, here is what I was thinking:

Clients
- Id
- Notes

CONTACTS
- id
- client_id
- address
- phone number

Now I am trying to avoid a third join table like this:

Clients_Contacts
- id
- client_id
- contact id

because I still want a one to many and not many to many relationship between contacts and clients. Is there something wrong with the way I am thinking about this? Can someone please help me design this database and show me what a sample query querying multiple addresses would look like.

Upvotes: 1

Views: 4057

Answers (3)

KingCronus
KingCronus

Reputation: 4519

Why would you nead a join table?

I would have

Client
- ID (PK, FK of Contact)
- Notes

Contact
- ID (PK)
- client_id
- address
- phone_number

Then just set the ID of the client to be the value of client_id within the contact information.

This way, multiple Contact records can have the same client_id.

Upvotes: 2

Danny Lagrouw
Danny Lagrouw

Reputation: 418

You say you want to store multiple addresses and phone numbers per user (= client, I assume?). Your current model already supports this. Every client can have as many contacts as needed (each contact would have the client_id set to the client's id). Only if you want to assign a single contact (address/phone number) to multiple clients as well would you need the third table.

Upvotes: 1

RpgNick
RpgNick

Reputation: 110

Your first idea looks fine for your use-case (one to many). You'll only need the third join table if you want a many to many relationship, which you do not want.

A sample query to get all addresses from a specific client would be:

SELECT address FROM Contacts WHERE client_id = 1

Upvotes: 1

Related Questions