Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Development Tools

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,836 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

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

Yusuf Bagha
Yusuf Bagha
24,370 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