1

I'm thinking about the right database design for this requirement.

It's a manual maintenance check. A company can have a maintenance contract comprising HW checks, Sys checks, both or none. The part with the CommandParameters worries me especially. The amount/name of the commands can be changed after a while, furthermore they are depending on each company. I was thinking to outsource the CommandParameters to it's own table, linking it with the MaintenanceContract. The commands would be all in one column, seperated with a delimter. The checkbox and comment line (free-text) are not directly linked with them (see below). Problem here is, I have to be sure that the amount of delimiters of free-text, checkbox, commands are always the same. I guess it's not possible in the structure, which I have designed below. Would be nice, when you have some other/better ideas.

enter image description here

Here the database draft:

enter image description here

  • It's not very clear what you're asking. This question seems very broad. Please improve to it by editing to add focus to particular issues. – candied_orange Aug 25 '16 at 15:09
  • What is MachinesInfo ? – Tulains Córdova Aug 25 '16 at 16:58
  • MachineInfo, its a terminal computer, at the moment, we have only companys with one terminal computer so I decided for a 1:1 relationship. – Matej Aug 26 '16 at 7:26
0

Errors:

  • The relationship between COMPANY and MACHINE should me 1:M and not 1:1
  • MAINTENANCE_HW and MAINTENANCE_SW are basically the same
  • The column COMMANDS violates NF1 by having multiple values separated by comma
  • The relationship between MAINTENANCE_TYPE and MAINTENANCE should be M:M and not 1:1 for a contract to include both harwd and soft as you state:

Recommendations:

  • MAINTENANCE_HW and MAINTENANCE_SW should be merged and then renamed MAINTENANCE_LOG
  • How do you know which row is hardware and what is software: through the FK with MAINTENANCE_CONTRACT->MAINTENANCE_TYPE
  • COMMAND_PARAMETER should be a separate table
  • Every item in MAINTENANCE_LOG is related to a command parameter

UPDATE:

  • Since we are normalizing COMMAND_PARAMETER (remember NF1 prohibits habing multiples values in one column. So we create master/detail construction where COMMAND is the master table and COMMAND_PARAMETER has all the parameters of that command. COMMAND basically has a an ID and the description of the command. COMMAND_PARAMETER has an ID and the description of the parameter.
  • I also corrected and error in my design. Now MAINTENANCE_LOG points to the join table CONTRACT_MAINTENANCE_TYPE. This way you know what if it's HW or SW and transitivelly to what contract it belongs to.
  • I didn't add all column names because it's a conceptual model, not a physical model. In that stage you still can correct many conceptual errors before actually creating tables. In this stage they are noy yet tables, but entities. Tables with a relationship pointing to a parent entity will have a FK and any other column that you want.

Now <code>MAINTENANCE_LOG</code> points to the join table <code>CONTRACT_MAINTENANCE_TYPE</code>

A physical, down-to-earth model:

enter image description here

  • Thanks for your new approach. I have currently a few problems to understand your structure. The relationship between MAINTENANCE_CONTRACT and MAINTENANCE_LOG, How I can assign that the MAINTENANCE_LOG is now assigned to Software or Hardware? Furthermore where is exactly the difference between COMMAND and COMMAND_PARAMETER? It could be easier for me, when you would add to those TABLES the attributes. – Matej Aug 26 '16 at 8:50
  • I added an update and corrected an error in my design. – Tulains Córdova Aug 26 '16 at 11:48
  • @Matej Added a physical model. – Tulains Córdova Aug 26 '16 at 14:03
  • The physical model helped me alot. Thank you for your help! I will reply maybe later again, when I have some Implementation problems. – Matej Aug 26 '16 at 15:02
  • To be sure, I want to ask about MAINTENANCE_LOG / COMMAND_PARAMETER / COMMAND. Could you improve me when I'm wrong: On your database design, there will be for each parameter (command1, free-text, checked/unchecked) an own entry in MAINTENANCE_LOG. The free-text from the maintainer get outsourced in COMMAND_PARAMETER. COMMAND contains only a general description about the COMMAND. – Matej Aug 29 '16 at 7:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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