In Exercise 4 Insert Referential Data, we add information into our database to make connections between tables.
We have added three rows into the person_pet table. Person with id 0 (in the person table) has two pets, the pet with id 0 and the pet with id 1 (in the pets table). Person with id 1 (That’s me!) has one pet, the pet with id 2 in the pets table. In SQL, comments are noted using /* comment in here */, like in CSS.
- Add the relationships for you and your pets.
Added on lines 8-10.
- Using this table, could a pet be owned by more than one person? Is that logically possible? What about the family dog? Wouldn’t everyone in the family technically own it?
Yes, a pet can be owned by more than one person. Logically it makes sense, because a family pet would be owned by all the people in that family. Similarly, a house can also be owned by more than one person.
- Given the above, and given that you have an alternative design that puts the pet_id in the person table, which design is better for this situation?
It is better to put the pets into a separate table from the person table. If we put the pet_id in the person table, a pet cannot have more than one owner. (Since the pet id is a primary key, it cannot be repeated in the column.) We would only be able to link one pet_id with one person_id if the pet_id was in the person table.