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 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?

share|improve this question
4  
en.wikipedia.org/wiki/Entity–attribute–value_model is what you're seeking –  FreshPhilOfSO Apr 22 '13 at 0:08
1  
I have a very similar question asked on Stack Overflow: How to design a database for User Defined Fields?. You might be interested in checking out the information posted there. I think the end result I went with was one table for all shared attributes, and then allowing users to make tables for the data attributes of generic known groups of entities (such as Books, Music, etc), and EAV for the rarer data attributes. –  Rachel Apr 23 '13 at 15:59
add comment

4 Answers

up vote 10 down vote accepted

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.

share|improve this answer
add comment

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
share|improve this answer
4  
Don't just throw such a sentence here. Such sentences aren't used to answer this type of question but to sell something. Use explanation on DBA.SE. –  dezso Apr 23 '13 at 9:30
4  
@PaddyCarroll While I disagree on the essence of the answer, I haven't downvoted for this. I have downvoted because your answer is too short. Look, even your comment response to Chris is longer than your answer. Please put that (and any other justification you have) for your answer and I will be happy to reverse my vote. I don't think anyone opposes to different views at this site but we do oppose to less explained and justified answers. –  ypercube Apr 23 '13 at 11:53
2  
You may be right, but for me to acknowledge that, a few points are missing: why NoSQL, what the advantages are against EAV, supertype-subtype, Chris's suggestion or other possible solutions (sparse tables etc.) –  dezso Apr 23 '13 at 11:54
4  
@PaddyCarroll please add some more substance to this answer, or I'll consider it a comment and convert it for you. "Don't do that" isn't an answer unless it also includes a "Do this instead" and you don't explain ANYTHING about why he should choose one implementation over another. –  JNK Apr 23 '13 at 12:58
1  
Great edit. Very informative. One minor thing I would add is that table inheritance is full of obscure gotchas in PostgreSQL. It can solve important issues (and may well be a part of a solution here) but it doesn't always work the way folks expect. Use it with caution. (My blog, ledgersmbdev.blogspot.com, has a fair number of posts on this sort of thing) –  Chris Travers Apr 24 '13 at 6:07
show 5 more comments

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...

share|improve this answer
1  
+1 for showing the good sides of EAV also. It certainly is a manageable model, but as you noticed, enforcing logic can get difficult (one birthdate and 3 phone numbers for example). Those who choose this way should do so knowing the cons... and pros! –  KookieMonster Apr 23 '13 at 13:10
    
@KookieMonster so the other way to do that would be, what? Three phone number columns and one Birthdate column? And if you want to add a 4th phone number you have to add a new column? As I said, enforcement of certain rules can be complex but I'll typically take that over a constantly changing schema, a perpetually adapting API, and ever-widening table. –  Aaron Bertrand Apr 23 '13 at 13:13
    
+1 as @KookieMonster said. The big thing is, IMO, EAV is really great for some things. We use it in some areas of the LedgerSMB code for example. I am pretty sure it is not the right place to start with this problem but it may well end up being an important piece of the solution. –  Chris Travers Apr 23 '13 at 13:18
    
I wholeheartedly agree. –  KookieMonster Apr 23 '13 at 13:18
add comment

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.

share|improve this answer
    
EAV works very well for some very specific types of data, where the data actually matches an EAV model. For example parameters for an HTTP request..... So while I am not prepared to call it an antipattern categorically, I totally agree that here it would be. –  Chris Travers Apr 23 '13 at 12:23
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.