CODEWITHSUNDEEP
database-designrelational-database
TMNuclear
TMNuclear

Reputation: 1175

Best database design using user defined fields

I have a question about the 'best' design solution in a case where a user defined table can specify the different types for an entity, yet 1 type within all these user defined types is something I want to keep record of separately, because this has a relationship to another (user defined field in a) table.

For example, there are employees. Employees have functions, defined by the user, but 1 of the functions is 'mechanic', and I want to keep records of mechanic, because mechanics have skills based on productgroups, which also are defined by the user, but in addition to that, mechanic skills can also be user defined!

Here is my example of tables and let me explain each individual table below:

enter image description here

Employees are all the people known in the database as internal workers.

Functions are user defined records and represents every possible function the user wish to add for an employee. Mechanic is a pre-defined function record, and also another table for that to be able to keep extra records of it.

Junction table between Employees and Functions.

Employees whom are set to the function "Mechanic" will have the option to choose from available skills. These skills involve custom created skills in CustomMechanicSkills with no link to any table whatsoever AND skills defined by ProductGroups (see below).

ProductGroups are user defined group of products. These group of products will need servicing and maintenance either by a supplier or own company. In case if it's the own company, it's good to know which employee has the required skill to be able to perform the maintenance for it - therefore the relation between ProductGroups and Employee_MechanicSkills. Additional MechanicSkills can also be created for the mechanic.

User defined mechanic skills. User might not only want to link mechanic skills to the existing productgroups but also additional, custom requirements, apart from the productgroups.

Junction table between Employees and CustomMechanicSkills

I've been thinking about this for a while now. In my database design, data integrity is the key, thus I tried to normalize as far as possible. But on the other hand, unneeded complexity of data structure isn't what I want either.

I'd like to hear some of your opinions and different views about the pro's and con's of this design, and maybe hear or see a better, improved design if there is. I would really appreciate input.

Thanks.

NOTE: Naming convention is not totally applied to this model yet.

Upvotes: 3

Views: 3815

Answers (1)

Neil McGuigan
Neil McGuigan

Reputation: 48256

  1. Start with solid existing data model patterns, to minimize the need for this.

    https://dba.stackexchange.com/questions/12991/ready-to-use-database-models-example/23831#23831

    Make sure you understand table inheritance.

  2. Consider allowing the user to alter their own database schema. You could, for example, bust out some reloadable Groovy for data structures, Hibernate for DDL migrations, and Roo HTML generation and automate this.

  3. Or consider XML or JSON database columns with indexing (possibly in another table). PostgreSQL 9.4 is out soon and has some good/fast JSON handling.

    Read this: http://martinfowler.com/bliki/UserDefinedField.html

    and this http://www.slideshare.net/billkarwin/extensible-data-modeling

  4. Don't consider EAV except as an absolute last resort.

Think hard about the fields that you might need to index, search on, sort on, count, and what sort of data integrity you require.

Upvotes: 4

Related Questions