Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am designing a database structure for a mailing list.

The data I need to record is the mail recipient data:

Since the mail recipient data is not predefined, it is causing a problem.

For example, some of the recipients have name, mail address, phone number, while others have name, birth date, mail address, gender, etc...

So my design is like this:

Table Receiver:
PK: ReceiverID

Table Attribute:
PK: AttributeID

Table Data:
PK: DataID

An example of one recipient is:

Receiver: 1 1 1
Attribute: 1 mailAddress

Receiver: 1 2 2
Attribute: 2 Name
Data:2 Tony

This is causing a problem because I want to check the data field type and length. What is a better database design?

Sorry about the poor description, I will clarify it if you are confused.

share|improve this question
(mailAdress, Name) is a possible attribute combination for a record. What other combinations are possible? Can you give further examples? Why are the attribute gathered, how is the data used? – miracle173 Sep 5 '12 at 4:09

Whenever possible, you want to avoid the EAV design, which is basically what you are proposing.

EAV has it's place, but it's overused. Essentially you are adding a layer of abstraction to your data by storing the meta-data (describing the actual data) in the data tables, then having to query the meta data before you can actually get at the real data. It gets incredibly complicated very quickly.

For your (relatively simple) use case, you should make a "normal" table along the lines of:

ReceiverID, ReceiverName, ReceiverAddress, ReceiverBirthdate...

As long as you have a couple of MAIN fields (I'm assuming you will want at a minimum an address and an ID), the others can be populated or NULL depending on if you have data for those.

There's no need to use an EAV model for something so straightforward, especially if you want to use it just to account for missing data.

share|improve this answer

I'm a strong believer that the entity-attribute-value design pattern is not generally suitable for databases, you should rather consider the entities you want to model carefully and the use of nullable vs non-nullable columns. I see that there is a stack overflow thread about EAV.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.