1

I have already created a database design with a set schema. The database schema is for a web quotation form. PS it is not a simple quotation form and hence need to customise the form, form builders like Gravity Form have been tried and suitable in this case.

I have about 15 tables (mostly in 2NF and 3NF) with set attributes and datatype.

But the question is should I stick with the traditional normalized schema design or use EAV design instead for some tables? It seems Gravity Form uses EAV DB design but that is due to the dynamic nature of a form builder.

This question has come up due to the fact that later on a attribute may need to be added, removed or updated from one or set of tables. The fields in the quotation form are reasonably standard with research done and I don't imagine modifying the fields and hence the attributes in the DB often.

I have read there are concerning pitfalls of EAV design. So if the fields are not going to changed often then is a set schema design with relationships useable?

Also in the event of modifying the schema is ALTER table going to cause any issues? If one adds a attribute to a table with ALTER table what would the previous data sets in the table have the value of the new attribute? What are the alternative of ALTER table?

Improve this question
3
  • PS: My tables contain 5-20 attributes. One table contains about 35 attributes. I don't imagine large records, perhaps few thousands per month to start with.
    – Clint Clive
    Commented Feb 21, 2015 at 22:18
  • I will stick with no EAV design. But if I want to change the schema (attributes in a table or relationships as examples) is ALTER table a good option? What are the implications?
    – Clint Clive
    Commented Feb 22, 2015 at 8:57
  • Please ask this as a separate question.
    – András Váczi
    Commented Feb 23, 2015 at 9:27

1 Answer 1

Reset to default
0

Well, wait until you see the ugly JOINs you will need. I'm a strong opponent of EAV. I have seen too many large EAV datasets groan under the inefficiencies. However, since you have a tiny dataset, you might not run into the problems that others encounter.

Here's my blog on it, including one suggested alternative: http://mysql.rjweb.org/doc.php/eav

Improve this answer
6
  • Yes true. Actually I prefer with schema based design instead of EAV.
    – Clint Clive
    Commented Feb 22, 2015 at 8:05
  • I think I will stick with schema based no EAV design. But the then if I want to change the schema (attributes in a table or relationships as examples) is ALTER table a good option? What are the implications?
    – Clint Clive
    Commented Feb 22, 2015 at 8:52
  • ALTER TABLE takes an amount of time proportional to the size of the table. Sometimes it is better to CREATE another TABLE and INSERT...SELECT to populate the new table from the old. This (1) allows expressions for transforming the data, and (2) lets you see the result without losing the original table.
    – Rick James
    Commented Feb 22, 2015 at 18:53
  • Thanks. If an attribute is added to the table what would the new attribute's value be for the rows of records that were there when that attribute wasn't present? The new records would obviously be filled with the value for the new attributes as the form is submitted (ie. after the insertion code on form submitted is updated)
    – Clint Clive
    Commented Feb 23, 2015 at 1:02
  • Suggest you declare the new column to be NULL, in which case it will be NULL. If there happens to be a way of 'computing' the value of the column for some rows, then use an UPDATE to do that.
    – Rick James
    Commented Feb 23, 2015 at 2:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.