Apologies if this isn't the norm, but I am seeking advice for the database design for 'dog rescue/adoption'.

I've done my normalization research and this is my first attempt at database design. Any tips/feedbacks/criticism?

The trickiest part was dealing with dogs/users/organisations and representing who owns a dog.

  1. a dog can belong to an organisation. An organisation can have many organisation users. Think animal shelter/pound

  2. a dog can belong to an organisation user. think a one man band with a large property housing multiple dogs at a time

  3. a dog can belong to a single user, not attached to any organisation. think someone moving overseas and they need to find their dog a new owner

So, the dog table has to have either an organisation ID or a user ID attached to it, or both. Perhaps a trigger stating that one must be not null.

Dog Adoption/Rescue Database Design ERD

  • Can there be multiple pounds under one organization, for example a city who has multiple pounds? Can a dog be moved around different pounds?
    – SQLburn
    Dec 8 '15 at 23:31
  • the pound would be the organisation itself. it would be possible for a dog to be moved from one pound to another pound. I believe in this situation it would just be an update of 'organisationID' for that particular dog. Dec 8 '15 at 23:46
  • thanks! in regards to the question, that is correct. In this situation a dog is owned by either an organisation or a user. a user can also belong to an organisation. Dec 10 '15 at 1:35
  • thanks for that link. it is a similar scenario. I have some reading to do in regards to subtype/supertype. Dec 15 '15 at 4:31

You can have organisation, user, and location all have a reference to a common table representing something that can be an owner to a dog. Then dog can have a single reference to this table. Here is a diagram explaining.

enter image description here

dog.owner_id references can_own_dog.can_own_dog_id, in case that isn't clear. Each of the can_own_dog foreign keys is unique in their table, ideally unique across all three tables.

I'm not sure if you're familiar with object-oriented programming, but the idea is similar to multiple classes implementing a single interface.

On another note, your diagram says "Users can have multiple locations", and "Dogs can have multiple locations". I think these mean to say "Locations can have many users" and "Locations can have many dogs"?

  • Thanks for that updated diagram. Should I keep the 'organisation_x_users' table in the case I want to look up what user is a part of what organisation? (see 2. requirement of OP 'a dog can belong to an organisation user. think a one man band with a large property housing multiple dogs at a time'). In response to your 'on another note' question, yes, you're right, I got my phrasing wrong. As for the rest of the tables outlined in my original screenshot - do you think my database is sufficiently designed? Dec 9 '15 at 5:11
  • Yes, you should keep organisation_x_users, the diagram i posted has that table. I didn't include all of the columns, or its relationship between user and organisation to simplify the diagram. The rest of the database design looks good. Dec 9 '15 at 16:50
  • Ah yes, you're right. OK, your diagram makes sense. This is called a subtype/supertype relationship, I think. Just to confirm, can_own_dog.can_own_dog_Id is the primary key which then has 3 foreign key references in each of the 3 organisation, user,organisation_x_users table? Dec 15 '15 at 4:30
  • yes to the primary key foreign key question.It's sort of subtype/supertype, but I would think of it more of an interface/implementation model. For example, another similar scenario can pop up where multiple entities can give funding for a dog. You can create a can_fund_dog table (interface), and then user and organisation could have foreign keys to that table similarly to the way it does for can_own_dog. Dec 15 '15 at 16:03
  • I just realized that I didn't make it clear that the can_own_dog_id field should be unique in organisation, user, and organisation_x_users. I'll update the answer. Dec 15 '15 at 16:04

your table is fully normalised so that's good.

as for who lists a dog for adoption, you could use a bridging table between the organisation and user table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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