How to create a database for unknown kinds of data?

Question

I am in the process of designing a database for a new PHP/MySql-based application.

My problem is that I do not and cannot represent what should be saved in the database because it is unlimited and changing.

Here is the problem example: The application will be a shopping website that has many kind of products all of them have some shared attributes such as title and price but some kinds have specific details such as expiry date some have isbn some non.

This is just an example but I really have many kinds with many different attributes.

I can create a table for each kinds, but what I have is not all the available kinds, many kinds of items are unknown at this time.

Is their a way to accommodate this problem without over head in the user's side?

Answer 1

Unknown kinds of data sounds to me somewhat fishy. Your examples, of course, are all knowns. For goods and services careful analysis and normalization are important and I think you can get away from EAV modelling (which I think will cause mor problems than it solves) for core data. The rest could be stuffed in XML fields or the like. Additionally if you do your design right you can always expand the information appropriately. Consider the following three tables:

CREATE TABLE products (
    id int autoincrement primary key,
    sellprice numeric,
    part_code varchar(10),
    title varchar(32),
    description text
);

CREATE TABLE barcode_type (
    id int autoincrement primary key,
    label varchar(15) not null unique
);

CREATE TABLE make_model (
    id int autoincrement primary key,
    make varchar(15) not null,
    model varchar(15),
    barcode_type int references barcode_type(id),
    barcode varchar(32)
);

Now with this you can assign barcodes (including ISBN, EAN, UPC, etc, to various parts, one per make/model combination. If you need to support more barcode types, this is not hard to add. As far as expiration dates, where these go depends on where you are tracking them. If you want to have temporary pricing, or pricing for groups of customers, you can add that too.

However what you are describing doesn't sound very unstructured. I would suggest starting with a minimal design and expanding as needed rather than an EAV design and later regretting it.

Answer 2

There are some SQL Server specifics here, but I give my spin on EAV in general. It is not the devil it is often made out to be, and several of the typical excuses problems can be avoided. For example, @KookieMonster said you can't enforce that a user doesn't have two birthdates, but that is easy:

CREATE TABLE dbo.Users
(
  UserID INT PRIMARY KEY,
  Username NVARCHAR(255) UNIQUE
  --, ...
);

CREATE TABLE dbo.Properties
(
  PropertyID INT PRIMARY KEY,
  Name SYSNAME UNIQUE
  --, ...
);

CREATE TABLE dbo.UserProperties
(
  UserID INT FOREIGN KEY ...,
  PropertyID INT FOREIGN KEY ...,
  DateValue DATE,
  IntValue INT,
  -- ...
  PRIMARY KEY(UserID, PropertyID)
);

(Again, this is SQL Server syntax but hopefully the concept resonates.)

If the logic is more complex than that (e.g. they can have three phone numbers but only one birthdate), then it gets a little more convoluted, but you can still enforce things that match your business logic using triggers, stored procedures, etc. I don't know how any other solution will solve this problem better while simultaneously not introducing others.

Performance can be a problem, however we solved this in SQL Server 2008+ using filtered indexes (for specific properties) and lazy materialization of denormalized versions of the tables. For sets of properties that are slowly changing, it is easy to make background processes that will flatten out the tables so that for certain or all products you have a materialized, pivoted version of the data to avoid all the joins. How that would work in MySQL I'm not quite sure, so I won't provide syntax, but perhaps I will blog further about this from the SQL Server perspective...

Answer 3

Dont use MySQL, a relational database is not used for the solution of this type of problem. Use a document or NoSQL database such as MongoDB or possibly RavenDB on windows.

Or alternatively use PostgreSQL. If you have a base set of attributes you can build inheritance into your tables

create table base_items
( id bigint,
title varchar(50),
price money)

then for the other items, say books or food

create table book_items 
(isbn varchar(20))
inherits (base_items)

create table food_items (date expiry_date)
inherits(base_items)

make your data

insert into base_items (id,item,amount) values
(3,'soap',0.99);

insert into food_items (id,item,expiry,amount) values
(4,'banana','2012-01-01',0.50);

insert into book_items (id,item,isbn,amount) values
(1,'some book','ABC-000-02100',20.99);

insert into book_items (id,item,isbn,amount) values
(2,'some other book','ABC-000-02102',20.99);

and

select * from base_items;
 id |      item       | amount
----+-----------------+--------
  3 | soap            |  £0.99
  1 | some book       | £20.99
  2 | some other book | £20.99
  4 | banana          |  £0.50


 select * from book_items;
 id |      item       | amount |     isbn
----+-----------------+--------+---------------
  1 | some book       | £20.99 | ABC-000-02100
  2 | some other book | £20.99 | ABC-000-02102


select * from food_items;
 id |  item  | amount |   expiry
----+--------+--------+------------
  4 | banana |  £0.50 | 2012-01-01

Answer 4

If this database is in any way connected to what your consumers are buying, performance will soon be one of your most important issues. I am not saying that EAV has no place in the database world, but you're likely to bring more problems than answers with this model. As I have to manage one such (3rd party) database myself, here are a few things to keep in mind:

• Performance can get bad pretty fast: If you want to retrieve all the fields for a given product, customer... you will have to multiply the LEFT JOINs, as each attribute will be stored in a different row. Now imagine when you have hundreds of fields to joins.

• data integrity: it will be tough to enforce. For example, no one is preventing the customer from having two (or more) birth dates. If isbn is required for books, how will you make sure it is? What will be the field type of your birthday field? You can have a lot of code to help you with this, but it will be hard and long to write and will surely impact the performance.

The list can go on, and my favorite reading on the subject is Pragmatic SQL Antipatterns by Bill Karwin. You can also have a look at this video, SQL Best Practices in less than 20 minutes. Our vendor can not change their architecture now (would require months of redesign) and problems are piling up for our data volume. Weight the pros and cons carrefully before going down this road.