CODEWITHSUNDEEP
databasedatabase-design
ryandlf
ryandlf

Reputation: 28555

Database Design for Expanding Lists

Admittedly, I am simply looking for some direction here. I have a specific situation, and being a novice in database design I am lost on how to begin tackling this problem. Let me start by explaining my situation.

I have a mysql table called contacts. As the name infers, it stores a list of contacts and the attributes that go along with each such as first name, last name, email, phone number etc. I would like users of my application to be able to add an unlimited amount of certain attributes for each contact. So, for instance rather than a contact having one phone number, the user could add another number, and another if they choose etc so essentially, a contact in my database can have as many phone numbers as the user needs. This will also be true for other fields in the table, but for the sake of simplicity let's just stick with phone number as an example.

So what is the best way to approach this? Should I have a separate table called contactsPhone and have a matching id column so that any number of rows in the phone table can be associated with one row in the contacts table? Or is there a way to store an ArrayList of some sort in the contacts table so I can have multiple phone numbers in just one field?

Upvotes: 0

Views: 250

Answers (2)

Oded
Oded

Reputation: 499242

You should be looking at modelling something like this in a document database - a relational database is a poor choice for a flexible schema. You may be able to just have this specific portion of you data in a document database.

If you must, the common solution is the entity-attribute-value pattern - note that this requires multiple joins, makes ad-hock queries difficult and is generally slow.

Update:

I misread the question a bit - if you do know which attributes you want to hold multiple values and this list will not change (or not change much), entity-attribute-value may not be the best way forward.

A one-to-many table per each of these attributes will work (and is a standard relational solution for this kind of problem) - each such table will require a foreign key to your contacts table and a column to hold a single attribute value. This allows you to have multiple attribute values against a single contact.

Upvotes: 3

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95731

I would like users of my application to be able to add an unlimited amount of certain attributes for each contact. So, for instance rather than a contact having one phone number, the user could add another number, and another if they choose etc so essentially, a contact in my database can have as many phone numbers as the user needs.

You're not describing an unlimited number of attributes for each contact. (That's a Good Thing.) You're describing an unlimited number of rows for a single attribute, in this case a contact's phone number.

So, yes, a table of contact phone numbers would work well. You might want to give some thought to how the user might want to identify phone numbers. For example, do they need to distinguish home phone numbers from work numbers and so on.

Upvotes: 1

Related Questions