Database structure for a mailing list.

Question

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.

Answer 1

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.

Answer 2

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.