Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools

Tiffany McAllister
Tiffany McAllister
25,806 Points
Posted by Tiffany McAllister
Tiffany McAllister
25,806 Points

Database Design Help

Hi everyone,

I have to design a database schema for some dog show events and I'm a bit confused as to how to structure it. So far I have the following tables:

  • Breeds
  • Owners
  • Judges
  • Purebreed Dogs
  • Crossbreed Dogs

I'm not sure how to structure the events and classes ? Any help or suggestions would be great. The information I have is below:

Dogs Info produces catalogues for events run by the dog clubs of NSW.

Each event has a number of classes – these are a subset of all available classes, chosen by the club running the event. Each class is judged by a registered judge. The class name and the judge name must be listed in the catalogue.

The dogs entered in each class must be listed according to the rules of Dogs NSW:

For purebred dogs, the listing in the catalogue must contain:

  • The dog’s name
  • Its registration number with Dogs NSW
  • Its parents’ names
  • Its breed
  • Its gender
  • Its date of birth
  • Its height
  • The names of its owner(s)

For crossbred dogs, the listing in the catalogue must contain:

  • The dog’s name
  • Its registration number with Dogs NSW
  • Its height
  • The names of its owner(s)

A dog may be entered in more than one class. Every dog entered in the event will be given a unique number for that event.

2 Answers

Ron McCranie
Ron McCranie
7,837 Points
Ron McCranie
Ron McCranie
7,837 Points

So you're off to a good start. I might combine all dogs in one table and have a field that indicates whether they're purebred or mixed (isPurebred) where 1 = true and 0 = false (boolean). That way you can work with one Dogs table throughout the project but then filter by the "isPurebred" field when you need to know that information. In addition to the tables you already mentioned I'd do "Events", "Classes", "EventClasses", "DogsInClasses"

Fields by table: Classes [ id, name, etc... ] // this is the master class table, look at is as a template that will be referenced for each event (see EventClasses) Events [ id, name, date, etc... ] // this is referencing a specific event EventClasses [ id, class_id, event_id, judge, etc... ] // this one is where you reference a Class from the Classes table, by it's id, and then give the event specific details in this table. DogsInClasses [ eventClassID, DogID ] // you're just relating a specific dog from the Dogs table with a specific class from the EventClasses table. This is why you want all the dogs in one table.

Hope this gets you started.

Tiffany McAllister
Tiffany McAllister
25,806 Points
Tiffany McAllister
Tiffany McAllister
25,806 Points

Thanks so much for your suggestions, Ron! It definitely helps! :)

Yusuf Bagha
Yusuf Bagha
28,853 Points
Yusuf Bagha
Yusuf Bagha
28,853 Points

You could create 1 MySQL page in 1 database, but I believe it would be easier to make 5 MySQL pages and link them in 1 Database.

Sorry for the improper terminology, I haven't done MySQL in months