0

Good evening, I write you after days of "thinking": D

I'm working on a WFM system that allows you to manage activities from the field. But now the request to manage several different activities forced me to redesign the whole DB. Originally only 2 types of activities were handled (Installation, Failures) and all was managed by a single database table with all the columns of one and the other activity. The unused column for a task assumed the null value and was not shown via PHP.

Now I have to understand how to structure a db that has the following characteristics: - the user can configure endless types of different activities (Installation, Failure, Gardening, Reclamation, etc ...) - the user can configure infinite properties / attributes (Client name, Surname, Address, Expiration date, etc ...) - for each activity can be associated many properties (certainly not all) - each property plus being associated with many activities (certainly not all) - each property can take as many values ​​as it is applied (N ° values ​​= property X activity to which it is applied) - the user does not have to choose the table in which to insert the property, whether this is called "Customer cousin name" or "IBAN for payment"

Making a practical example I can have that the properties of customer registry are used for each activity, but maybe the property "Height grass" is used only for the activity "Gardening"

Can someone help me? Thank you

Rick James
  • 135,179
  • 13
  • 127
  • 222
Nixiam
  • 43
  • 7
  • 1
    Do you have a question? You might wish to explore the EAV model. While it has its drawbacks, sometimes it is the most appropriate solution for these sorts of scenarios. – Strawberry Oct 26 '18 at 18:10
  • Thank you, I did not know that this kind of structure was called EAV :D It seems like a good method, but I would like to know if there are other ways, maybe that make better use of the database – Nixiam Oct 26 '18 at 18:30
  • 1
    Well, if I find myself using EAV then I structure my data so that I have a table for integer attributes, a table for date attributes, and a table for string attributes, etc, so at least I can take advantage of data types. – Strawberry Oct 26 '18 at 18:33
  • People propose EAV to implement application functionality that is DBMS-like--limited table manipulation & querying. For some reason they start [hacking together a DBMS](https://stackoverflow.com/a/23950836/3404097) & it does not occur to them to [*just delegate to the DBMS they already have*](https://stackoverflow.com/a/32285603/3404097). – philipxy Oct 27 '18 at 03:05
  • Possible duplicate of [Alternatives to Entity-Attribute-Value (EAV)?](https://stackoverflow.com/questions/4049159/alternatives-to-entity-attribute-value-eav) – philipxy Oct 27 '18 at 03:13

0 Answers0