How to Structuring tables for a database

I am not sure if I am doing this right.

This is a database schema that is being planned. Pets can go to multiple places. For example, they can go to a shelter, a Garage, or a Foster.

Does the diagram make sense. Could this be done better?

This doesn’t quite sit right with me. To add a cat you have to both update the site_id in the cat table and then update the appropriate site table by adding an entry for the cat.id. This introduces the possibility that these two values could get out of sync (the site_id in the cat table could be pointing to the wrong site). I think it would be much better if assigning a cat to a place to live only involved updating one table.

I would rethink your schema a little. It’s obvious that you need a table for cats, but I think your site tables might need to be re-evaluated. I don’t think that you are placing a cat in a “site” but rather a “room” at a site. So I think that you should have a “room” table and then you can associate a cat with a room using a third lookup table or perhaps you could put a room column in the cats table. Regardless, now you would only need to make one table update to assign a cat to a room.

Now I’m only going off the information I can glean from your schema so I might be making some assumptions that aren’t valid because I don’t have all the information you have. But you definitely want to avoid storing duplicate information in separate tables.

This is the exact reason why I posted this. Thank you for your thorough answer. If I did end up putting a room the cats table, how I be able to associate that with the site table.

Every room belongs to a site, so you could have a site column in the room table.

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.