Mysql – Database structure for a mailing list.

MySQL

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.

Best Answer

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.

Related Solutions

Mysql – Handling newsletter mail subscribers data

For your first problem, you may have to do it in stages. Here's one approach:

Create the new INT column - BUILDING_ID_INT
Create a new table that maps the building name text to Integer values
Write a program to populate the new BUILDING_ID_INT column based on the data in the current Building column and the values in the table created in #2.
Re-write all of your existing code to use BUILDING_ID_INT.
Delete the old Building column.

For the second problem, where the database structure changes between the time a user makes a request, and the time they confirm their request, it will vary on the kind of change. If an old column is being removed, then there shouldn't be any problem because your current insert statement should not be inserting data that's not insertable. For new columns that are added, default values can be used of it's non-nullable, and process will have to be introduced to handle these. For small volumes, it can be manually asking these users to send the additional new information. For large volumes, you'll want to write code.

Mysql – Am I wrong in table design or wrong in selected index when made the table

It is difficult to say without seeing an execution plan, but I would take a look at your joins to the geography hierarchy. The geography tables (prov/city/district) have compound keys and all of the necessary foreign keys are already in your peoples table. You are currently joining partly directly and partly through the compound keys. This is a very unconventional approach. You should join on full keys not on partial compound keys. However, in you case you could probably simplify further. Instead of joining up the hierarchy why not just join directly from the bottom level to each piece of the hierarchy as in a star schema.

Try this join clause instead:

FROM peoples E
  JOIN test_prov B ON  E.id_prov = B.id
  JOIN test_city C ON  E.id_city = C.id 
                   and E.id_prov = C.id_prov
  JOIN test_district D ON  E.id_district = D.id 
                       and E.id_city = D.id_city
                       and E.id_prov = D.id_prov
  JOIN test_town A ON  E.id_town = A.id 
                   and E.id_distict = A.id_district 
                   and E.id_city = A.id_city
                   and E.id_prov = A.id_prov 
WHERE E.stat_valid=1 
  AND E.mark_as_trash=0

Note also that I've taken stat_valid and mark_as_trash out of the joins and put them in a where clause. Don't include non-key columns in your joins, it's bad form. Note too that these columns are not indexed, so you will be potentially be causing a table scan with these. I suspect that even if they were indexed they wouldn't be selective enough and you might end up with a table scan anyway.

Best Answer

Related Solutions

Mysql – Handling newsletter mail subscribers data

Mysql – Am I wrong in table design or wrong in selected index when made the table

Related Question