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.
Here the database draft:
3
Errors:
- The relationship between
COMPANYandMACHINEshould me 1:M and not 1:1 MAINTENANCE_HWandMAINTENANCE_SWare basically the same- The column
COMMANDSviolates NF1 by having multiple values separated by comma - The relationship between
MAINTENANCE_TYPEandMAINTENANCEshould be M:M and not 1:1 for a contract to include both harwd and soft as you state:
Recommendations:
MAINTENANCE_HWandMAINTENANCE_SWshould be merged and then renamedMAINTENANCE_LOG- How do you know which row is hardware and what is software: through the FK with
MAINTENANCE_CONTRACT->MAINTENANCE_TYPE COMMAND_PARAMETERshould be a separate table- Every item in
MAINTENANCE_LOGis 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_LOGpoints to the join tableCONTRACT_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.
A physical, down-to-earth model:
11