Mysql – Need advice for a ‘tag’ table design

One way to understand what's going on with normalization is to find out what normalization buys you. In other words, what's going to happen if you depart from any given normal form?

For normalization forms second through fifth normal forms, the answer is "update anomalies". That is, when you insert, update, or delete rows, can you end up leaving things in a bad way? One particular kind of bad way is a database that contradicts itself, that stores a given assertion somewhere in the database and stores a contradictory assertion somewhere else in the database.

Normalization helps with this by obviating the problem. That is, it cuts down or eliminates the possibility of contradictory assertions by assuring that facts are stored once, and only once. The fact is either right or it's wrong, but it's not self contradictory.

There are other ways to avoid self contradictory databases, but they involve careful, disciplined programming. There are situations where a non normalized approach gives you more benefits than a normalized approach. But this calls for judgement, and judgement comes with experience.

With regard to EAV, one attraction to EAV is that it allows you to get started without coming up with a logical model for the structure of the data. The reason is that any logical model whatsoever can conform to EAV. So why doesn't everybody do EAV? Because sometimes, the data that results is relatively worthless compared to the expected value when the database was built. I'm not saying EAV is always wrong. I am saying that it often is. Again, this calls for judgement, and judgement comes with experience.

Not sure why one of the answers made such bold statement . Regardless: everything can be modeled in relational model

create table entry 
( 
  id number(12) primary key, 
  name varchar2(256) not null unique,  
  customer_id number not null references customer(id)
);

create table datatypes
(
 id number primary key,
 name varchar2(256) not null unique
);
create table entry_data ( 
  id number(12) primary key, 
  datatype_id  number not null references datatypes(id),
  value     varchar2(256),  --or CLOB ,no index
  value_geometry geom  ,    -- no index
  --value_blob   blob -- if you neeed, no index
  entry_id number not null references entry(id),
);

your task is related to the multi-valued attributes task. Datatypes table will abstract your datatypes, however 2 value** columns are created in entry_data due to the fact that your data can be different in nature(belong to different Classes), thus would need different storage. But as far as varchar2 vs. number vs.date -- all can be placed into varchar2 , just would need later on some casting. And with very simplistic logic of NVL in code you can determine which column was populated out of value or value_geometry.