Is it better to have multiple tables with the same primary key or one big data table?

Posted on

Question :

So there is one primary key so far used for IDs of locations.

Currently there are multiple tables (sheets) in Excel for the following

  • Events (Location ID, Date, Title, Description)
  • Animals (Location ID, Species, Name, Population)
  • Restrictions (Location ID, Permits, Zone)

Is the above bad practice? From what I have read online you should not use the same primary key across multiple tables.

How should the tables be structured to follow “best practices”, have it be one big database table or is it fine to leave in multiple tables?

Answer :

As per mustaccio’s comment, when you have the same PrimaryKey defined in each of your Tables, that then guarantees a one-to-one relationship is enforced between each Table. In other words, based on the schema of your Tables provided in your question, you won’t ever be able to have more than one Animal at one Event, or more than one Restriction for a specific Animal, etc. They all share the same unique Location ID.

If that meets your needs then your design is ok.
But if you want to be able to have multiple Animals at multiple Events with varying Restrictions for different locations etc, then you might be better off with the following design:

Add a fourth Table called Locations with the Location ID PrimaryKey.

Remove the Location ID PrimaryKey (and unique constraint) from all the other tables and instead create a ForeignKey relationship between it and your Locations table on Location ID.

Add a new unique PrimaryKey field to the Events, Animals, Restrictions tables with the appropriate names (e.g. Event ID for the Events table, Animal ID for the Animals table, etc).

And to directly answer your main question, it’s better to keep these as separate Tables. That is called database normalization and is important for query efficiency and elimination of unnecessary data redundancy. In general, separation of concerns should be split into separate Tables.

Leave a Reply

Your email address will not be published. Required fields are marked *