Database Design – Multiple Tables with Same Primary Key vs One Big Data Table

database-designms accesssql server

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?

Best 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.

Related Solutions

Is it acceptable to have a surrogate/primary key of one table be the primary/foreign key of another table

If you are using composite primary keys, your foreign key references would also need to be composite keys. If the primary key of building is (buildingId, buildingFullName) then buildingRoom would need to have both a buildingId and a buildingFullName that would be the foreign key to building. Similarly, if (buildingId, buildingRoom, roomId) is the primary key of buildingRoom, you would need all three of those columns in buildingRoomAsset in order to create the foreign key. Defining the foreign keys this way doesn't make sense.

If you are adding a surrogate key to the table, you would almost always want to define that column and that column alone as the primary key of the table. You can add additional unique constraints to ensure that things like the buildingFullName are unique within the table. Adding the buildingFullName to the primary key not only forces the buildingFullName to be pulled through to all the child tables, making it much more difficult to update the data, it means that you can have many buildings with the same name which seems unlikely to be what you're after.

Additionally, I would strongly suspect that you want an asset table that stores information about assets and then a roomAsset table that maps a roomId to one or more assetId values. That will make life much easier when an asset gets moved from one room to another among other use cases.

If it were me

building
------------------
+ buildingId <<SK>> <<PK>>
+ buildingFullName 

room
------------------
+ roomId <<SK>> <<PK>>
+ buildingId <<FK>>
+ roomNumber

asset
------------------
+ assetId <<SK>> <<PK>>
+ assetName

roomAsset
------------------
+ roomAssetId <<SK>> <<PK>>
+ roomId <<FK>>
+ assetId <<FK>>

Sql-server – Relationship that are optionally more specific

Instead of:

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(LocationID) REFERENCES Locations(ID)

I would do this:

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(EventID, LocationID) REFERENCES Locations(EventID, ID)

That would guarantee that "if the LocationID is specified, then that LocationID should be a Location that belongs to the specified EventID".

That done, your approach makes sense.

Best Answer

Related Solutions

Is it acceptable to have a surrogate/primary key of one table be the primary/foreign key of another table

Sql-server – Relationship that are optionally more specific

Related Question