-1

We're developing a system that helps users with managing their maintenance activities, mostly performed on machines and such.

Users can add machines to their catalog. Different machines need different maintentance schemes, but some of them should be able to share the same maintenance scheme, while keeping machine specific maintenance status. This functionality is basically reminding users when to perform certain activities.

Each scheme consists of multiple activities that should be performed every N days/months.

So when user defines a maintenance scheme it should look something like this:

Maintenance Scheme - Machine type RA1
Activities:
Activity1 - perform every 1 month
Activity2 - perform every 6 months
Activity3 - perform every 12 months
...

Each machine has it's own maintenance status:

Maintenance Status - Machine RA1-004
Activities:
Activity 1 - perform in 7 days
Activity 2 - ok
Activity 3 - Warning! Perform asap
...

Now every time I try to wrap my head around the database design for this use case I:

  • come up with a spaghetti like structure
  • come up with loosely tied structure
  • both happen at the same time

Example: enter image description here

This structure allows to quickly identify chosen scheme and current maintenance status, but on the other hand propagating changes to schemes seems like a huge pain. Is my approach anywhere close to being optimal? Is there any way to make it easier to propagate changes and simplify this design?

gradle
  • 113
  • 2
  • 11
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Nov 01 '19 at 02:23
  • This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Nov 01 '19 at 02:26
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. – philipxy Nov 01 '19 at 02:26

1 Answers1

0

I think IMHO, this E-R is normalized upto 3NF.

I also find that:

  • There is no functional dependencies between fields
  • No repeating records
  • There is no Many to Many relationships

AND, I think it can not be simplified more than that according to the Relational Model because:

  • User links to machines and also users defines the actions and activities
  • The relationship between machines and these actions is defined through the status in the shemestatus

This diagram supports also queries such as: Which machines needs maintenance, what is the status of each machine, etc..

I think the E-R is optimal/Good because each entity is mandatory and supports the expected queries.

Ibrahim
  • 71
  • 6
  • Those bullets are useless for addressing 3NF. And your "optimal" claims are unfounded since there are many reasonable design idioms for representing such situtations (see the duplicate link on the question) & 3NF is irrelevant to choosing among them. – philipxy Nov 01 '19 at 02:23
  • @philipxy,regardless of the usefulness of my points I want to ask: First:Is this model in 3NF or not. Second: from your mentioned reference, I quoted this EDIT by **PerformanceDBA** "If you are interested in "best practice", most of the answers are simply incorrect. Best practice dictates that the RDb and the app are independent; they have completely different design criteria.", so it is unfounded in your opinion may be, but since the question does not define optimality, I consider it non redundancy, supporting the required queries, and simplicity of the design and the APP to deal with it. – Ibrahim Nov 01 '19 at 14:44
  • @philipxy, the other thing, you menssion a link about inheritance, I would rather want to know what is the relevance of inheritance here, the question asked about simplicity and I did not see (correct me if I am wrong) any entity could be inherited from another entity. – Ibrahim Nov 01 '19 at 14:54
  • @philipxy, you said "there are many reasonable design idioms", could you please mention some of them even just titles, because I am interested to know them or should I create a separate question for this. – Ibrahim Nov 01 '19 at 14:58
  • @philipxy, you said "3NF is irrelevant to choosing among them", I am not advocate for 3NF or normalization because I saw systems running on supermarkets denormalized and only one or two tables are running with all the fields of POS using Clipper and dbaseIII+ dbf and this design does not rely on any rules, but it is practical and working for many years for large number of users. But here I used it because it is the MAIN standard of RDBMS and I see it relevant, and I did not want to open a debate about Nosql. – Ibrahim Nov 01 '19 at 15:44
  • I removed some words and modified the others in my answer just to be more precise. – Ibrahim Nov 01 '19 at 23:33