13

Im working on a project. Its mostly for learning purposes, i find actually trying a complicated project is the best way to learn a language after grasping the basics. Database design is not a strong point, i started reading up on it but its early days and im still learning.

Here is my alpha schema, im really at the point where im just trying to jot down everything i can think of and seeing if any issues jump out. http://diagrams.seaquail.net/Diagram.aspx?ID=10094#

Some of my concerns i would like feedback on:

Notice for the core attributes like area for example, lets say for simplicity the areas are kitchen,bedroom,garden,bathroom and living room. For another customer that might be homepage,contact page,about_us,splash screen. It could be 2 areas and it could be 100, there isn't a need to limit it.

I created separate tables for the defaults and each is linked to a bug. Later i came to the problem of custom fields, if someone wants for example to mark which theme the bug applies to we dont have that, there is probably a 100 other things so i wanted to stick to a core set of attributes and the custom fields give people flexibility.

However when i got to the custom fields i knew i had an issue, i cant be creating a table for every custom field so i instead used 2 tables. custom fields and custom_field_values. The idea is every field including defaults would be stored in this table and each would be linked to the values table which would just have something like this

custom_fields table
id            project_id        name
01            1                 area(default)
12            2                 rooms(custom)
13            4                 website(custom)

custom_field_values table
id            area           project_id    sort_number
667           area1          1             1
668           area2          1             2
669           area3          1             3
670           area4          1             4
671           bedroom        2             1
672           bathroom       2             2
673           garden         2             3
674           livingroom     2             4
675           homepage       4             1
676           about_us       4             2
677           contact        4             3
678           splash page    4             4

Does this look like an efficient way to handle dynamic fields like this or is there other alternatives?

The defaults would be hard coded so you can either use them or replace with your own or i could create a another table to allow users to edit the name of the defaults which would be linked to their project. Any feedback is welcome and if there something very obvious with issues in the scheme please feel free to critique.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user1547410
  • 863
  • 7
  • 27
  • 58

2 Answers2

24

You have reinvented an old antipattern called Entity-Attribute-Value. The idea of custom fields in a table is really logically incompatible with a relational database. A relation has a fixed number of fields.

But even though it isn't properly relational, we still need to do it sometimes.

There are a few methods to mimic custom fields in SQL, though most of them break rules of normalization. For some examples, see:

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks Bill, would you recommend a book or resource that explains with examples how i transform my design to incorporate Class Inheritance, i dont quite understand what it is yet or how to implement it with MySQL but im starting reading up on it now since you suggested that as the preferred option in one of the posts i linked to, if there is an online resource or book that explains this for a newbie then i will happily buy it, so far im struggling to find good explanations or examples. – user1547410 Aug 25 '13 at 20:36
  • [Patterns of Enterprise Application Architecture](http://www.martinfowler.com/books/eaa.html) by Martin Fowler is the best book for Class Inheritance, and many other patterns you should know. – Bill Karwin Aug 25 '13 at 20:49
  • thanks, it does seem the more i look at it the more my structure is suited to a Nosql database, i havent used it before but it seems the more logical choice, anyone disagree? – user1547410 Aug 26 '13 at 01:25
  • Yes, a document-oriented database can support runtime-defined columns, but keep in mind that the design of a non-relational database requires that you know how you're going to query it. – Bill Karwin Aug 26 '13 at 05:08
0

I found this as was searching for something similar as the customer can submit custom fields for use later.

i settled for using data type JSON which i appreciate was not available when this question was asked.

Luke Robertson
  • 1,592
  • 16
  • 21
  • You might like my presentation [How to Use JSON in MySQL Wrong](https://www.slideshare.net/billkarwin/how-to-use-json-in-mysql-wrong) – Bill Karwin Dec 06 '19 at 00:21
  • @BillKarwin this is a very extreme example, its easy to make things look wrong if you use them for the wrong purpose – Luke Robertson Dec 06 '19 at 00:40
  • I don't think it's that uncommon, based on the questions I see from developers on Stack Overflow. They put a lot of data into JSON that really should not be in JSON. It would be much easier to query if they used normal tables and columns. – Bill Karwin Dec 06 '19 at 01:41