Help with missing primary key

Hi there. I’m thinking about creating a database using an excel file from a hospital equipment inventory a while ago. the main there is a sample from the many tables in the attached image. the columns are:

  • code - given by the hospital and found in a tag pasted on the equipment
  • location - where the equipment was found at the time the inventory was taken (won’t be using it)
  • name - name of the equipment
  • brand - brand of the equipment
  • model - model of the equipment
  • serial number - found on the equipment
  • does it work? - this was done to keep track of which ones had to receive maintenance
  • observations - text space to specify missing pieces, visible damage, etc.
    I’m planing to use MS SQL server management to do this project. So my question is: I’m thinking of using the code as primary key, given every device has its own. thing is, some of the devices are really old and the painting, tags and other identifiers have come off or even the personnel takes them off (don’t ask me why :sweat_smile:) so if I can’t generate a primary key in the same way as they did how can I adjust my PK to fit the pattern? (many devices are missing the code so taking them out is not an option). I thought of generating a new PK and leaving the code as another column but I don’t know if that is a good practice.
    Any of you guys have any recommendations?
    thank you for reading

I would use id’s for primary key.

Depending on your DB engine, you could make the code column nullable and unique. (most database engines can handle unique with multiple nulls). This way you can guarantee uniqueness for the codes that do exists.

2 Likes

I get what you’re saying but:
is this a valid solution for a real world application? (this is my first project involving databases)
wouldn’t it present problems later when creating other tables? (I’m thinking of creating a table for each hospital area)

I would advise against this. Figuring out tables name dynamically is a nightmare

You should make a table (possible table names: areas, departments) and then rows for each department in this table.

so you then have 3 tables: hospitals, departments, equipment

departments should have a hospital_id foreign key
equipment should have a department_id foreign key

then you should definitely use id’s, if you have null-able codes the ids really simplifying joins. (or fetching relations and then data parsing)