4

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?

Improve this question
4
  • What do you think the primary keys are in your "tables", and why?
    – mustaccio
    Commented Dec 8, 2020 at 17:18
  • The PRIMARY KEY constraint uniquely identifies each record in a table. So from my understanding each unique identifier would be the "Location ID", no? Please educate or correct me if I am wrong.
    – ExcelDragon
    Commented Dec 8, 2020 at 17:56
  • If that is so, you can visit each location only once, and each location can only have a single species. Whether it's true or not I cannot tell, as you don't describe your business requirements.
    – mustaccio
    Commented Dec 8, 2020 at 18:08
  • I should have mentioned and will update the question. In the current state and likely in the future, each site will only have one event, animal/mascot, and restriction. Let's say I would like to introduce new animals, how should the structure or primary key change to suit? Thank you for following up and asking for more questions I really appreciate it. This isn't really for business, but a personal fun learning experience for me.
    – ExcelDragon
    Commented Dec 8, 2020 at 18:25

1 Answer 1

Reset to default
5

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, but most importantly improved data integrity too. In general, separation of concerns should be split into separate Tables.

Improve this answer
2
  • 1
    As one new to SQL, I really appreciate this thought out answer.
    – Carlo Nyte
    Commented Feb 26, 2023 at 2:58
  • @WhoIsCarlo Glad you found it helpful!
    – J.D.
    Commented Feb 26, 2023 at 3:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.