Database design for a daily maintenance tool

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



  • 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:


  • 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


  • 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


Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *