3

I need to implement custom fields in a booking software. I need to extend some tables containing, for example, the user groups with dynamic attributes.

But also, a product table where each product can have custom fields (and ideally these fields could be nested).

I already made some searches about EAV but I read many negative comments, so I'm wondering which design to use for this kind of things.

I understand using EAV causes many joins to sort a page of products, but I don't feel like I want to alter the groups/products tables, each time an attribute is created.

Note : I use Innodb

Dan D.
  • 73,243
  • 15
  • 104
  • 123
student310
  • 61
  • 8
  • AFAIK, there's no silver bullet. It's all about tradeoffs. The key question is what you are going to do with that data. "Infinitely flexible" data would require equally infinitely flexible UI and lots of use cases. I would start with the use cases and then implemented as much as possible in a fixed schema, adding as little EAV extension as possible, weighing that against, e.g., XML fields. – full.stack.ex Oct 23 '12 at 09:33
  • Also, from experience, a lot of EAV-related queries can be generated using DB metadata. Add that to the equation. – full.stack.ex Oct 23 '12 at 09:36
  • Thanks. Yes it should be possible to search a product with given attributes, it adds some complexity when creating 'dynamic tables' – student310 Oct 23 '12 at 09:45

3 Answers3

2

The only good solution is pretty much what you don't want to do, alter the groups/products tables, each time an attribute is created. It's a pain, yes, but it will guarantee data integrity and better performance.

If you don't want to do that, you can create a table with TableName, FieldName, ID and value, and hold lets say:

TableName='Customer', FieldName='Address', ID =1 (customers ID), Value ='customers address'

But as you said, it will need loads of joins. I don't think it is a good solution, I've seen it but wouldn't really recommend it. Just showing because well, it is one possible solution.

Another solution would be to add several pre-defined columns on your tables like column1, column2, column3 and so on and use them as necessary. It's a solution as worst as the previous one but I've seen major ERPs that use it.

Mate, based on experience, anything you will find on this area would be a huge work around and won't be worth implementing, the headache you will have to maintain it will be bigger than adding your fields to your table. Keep it simple and correct.

bad_coder
  • 11,289
  • 20
  • 44
  • 72
Diego
  • 34,802
  • 21
  • 91
  • 134
  • Thanks. I need to look at this solution. I'm reading an other post where someone talks about one table per product. I think it avoids to have a lot of empty fields. With this method the UI would looks like phpmyadmin – student310 Oct 23 '12 at 09:41
  • one table per product? what do you mean? (just added a small little comment on my answer) – Diego Oct 23 '12 at 09:44
  • ok, I underestimated the complexity of your scenario. What you would need is one table per type of products lets say. Yes, the link you provided contains two very good apporaches – Diego Oct 23 '12 at 09:53
2

I am working on a project entirely based on EAV. I agree that EAV make things complex and slow, but it has its own advantages like we don't need to change the database structure or code for adding new attributes and we can have hierarchies among the data in the database tables.

The system can get extremely slow if we are using EAV at all the places.

But, Eav is very helpful, if used wisely. I will never design my entire DB based on EAV. I will divide the common and useful attributes and put them in flat tables while for the additional attributes (which might need to be changed depending on clients or various requirements), I will use EAV.

This way we can have the advantages of EAV which includes flexibility what you want without getting much trouble.

This is just my suggestion, there might be a better solution.

Vivek
  • 1,640
  • 1
  • 17
  • 34
0

You can do this by adding at least 2 more tables. One table will contain attribute unique key (attr_id) and attribute values, like attribute name and something else that is needed by your business logic.

Second table will serve as join between your say products table and attributes table and should have the following fields:

(id, product_id, attr_id)

This way, you can add as many dynamic attributes as you like, and your database schema will be future proof.

The only downside that queries now will have to add 2 more tables to be joined.

mvp
  • 111,019
  • 13
  • 122
  • 148