5

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

Improve this question
4
  • 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, 2015 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.
    – adumbdumbstupid
    Dec 8, 2015 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.
    – adumbdumbstupid
    Dec 10, 2015 at 1:35
  • thanks for that link. it is a similar scenario. I have some reading to do in regards to subtype/supertype.
    – adumbdumbstupid
    Dec 15, 2015 at 4:31

2 Answers 2

Reset to default
2

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"?

Improve this answer
5
  • 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?
    – adumbdumbstupid
    Dec 9, 2015 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.
    – Joe Borysko
    Dec 9, 2015 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?
    – adumbdumbstupid
    Dec 15, 2015 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.
    – Joe Borysko
    Dec 15, 2015 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.
    – Joe Borysko
    Dec 15, 2015 at 16:04
-2

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.

Improve this answer

Your Answer

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

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