Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
    AttributeID
    DataID

Table Attribute:
PK: AttributeID
    AttributeName

Table Data:
PK: DataID
    DataContent

An example of one recipient is:

Receiver: 1 1 1
Attribute: 1 mailAddress
Data:1 test@test.com

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
add comment

2 Answers

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
add comment

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 http://stackoverflow.com/questions/494158/best-beginner-resources-for-understanding-the-eav-database-model about EAV.

share|improve this answer
add comment

Your Answer

 
discard

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.