Relating multiple tables to main table and each other

[squirrly]

I'm rethinking my design and can't figure out what I need to do to make these relationships work. Hopefully this explanation will make sense.



I have my main table with many independent small tables related to it. Not a problem.

MAIN TBL
PetID (pk)
StatusID (fk)
ColorID (fk)
AgeID (fk)

I have other small tables that need to relate to the main table....

TypeID (fk)
BreedID (fk)
GenderID (fk)
PatternID (fk)

.....where Breed, Gender, and Pattern also need to relate to Type.

BREED TBL
BreedID (pk)
TypeID (fk)

GENDER TBL
GenderID (pk)
TypeID (fk)

PATTERN TBL
PatternID (pk)
TypeID (fk)

Do I use a junction table between the main table and these other tables that also relate to the Type table?

JUNCTION TBL
PetID (fk)
TypeID (fk)
BreedID (fk)
GenderID (fk)
PatternID (fk)


I'm an eternal rookie by trial and error, it seems, so thanks for your kind advice!


(PS - am working with Dreamweaver's server behaviors for inserting/updating records, so the design must comply.)

[orange]

Can you give a 5-6 line description of the opportunity/problem you are trying to solve in plain English?
What are the processes and things involved, and how they relate? Try to avoid jargon, make it simple so we can understand.

[squirrly]

The subject is lost and found pets. Each pet has a type (cat/dog/bird). Each type has a list of breeds, genders, patterns, etc. My dropdown lists for breeds, genders and patterns, etc. will depend on the type, but must also relate to that one pet.

Any way I envision the relationships kind of makes a mess, so I thought I'd ask for design advice. I just need a nudge on the right track and I can go from there, stick with what I have, or change direction. I have very little experience with many-to-many relationships, but a junction table seemed logical even though there would be numerous relationships tied to "type".

This is about the simplist explanation I can give without using the examples in my original post.

[orange]

I think you tables may be missing some text fields like name, address, description etc.
See if this model helps
http://www.databaseanswers.org/data_models/lost_and_found_pets/index.htm

[squirrly]

Um, I actually asked them to create that model for me. And it helped with types and breeds, but then it stopped there. Plus it got into stuff that doesn't pertain (vets, problems, treatment).

So going off that model, is it okay to relate other tables to the pet_type table? It just seems super messy with all these relationship "lines" going from several tables to one item in one table. But that's just visual... if it's functional, that's what matters.

I may decide to simplify my tables (and life) by easing up on details, which would probably make my users a lot happier as well!

[orange]

I was not aware that you had requested a model from Barry, and I see there are no visible business facts tied to the model. I am surprised that the model would go far beyond your requirements/business facts. But then again, modellers tend to do conceptual things to allow things to grow. It shows you logically/conceptually how these other "things" fit/relate. Of course you don't have to use the model that was created, especially if it goes outside your "scope". Use the pieces that are relevant to you. You can add whatever tables and relationships are necessary to support your business.

Do you have a complete list of business facts about your business?

If you want to read up on a procedure to work from business facts to a set of normalized tables and relationships, the article here http://www.rogersaccesslibrary.com/T...lationship.zip does that.


Your model will be about your business and your model should support all of the facts about your business. That is key to a useful, maintainable database. All those relationship lines with cardinality and optionality are critical to the database and referential integrity. The relationships, implemented according to the model, will prevent duplication and prevent creation of orphan records through some sort of ad hoc deletions.

Can you show a jpg of the tables and relationships you have currently? I take it that you did use the model or parts thereof that was created for you.

I did find a list of animals on a website that may be of use to you.

Dog
Cat
Rabbit
Small Furry
Bird
Scales, Fins
Pig
Barnyard

There is also a list of dog breeds there, but I'm sure you have a list of your own.

Here's another list I found that may fit somewhere in your database. From wikipedia

The basic types are companion dogs, guard dogs, hunting dogs, herding dogs, and working dogs, although there are many other types and subtypes.

Would you like to provide some description of what "pattern" means?
I'll do my best to help you with a model, but you'll have to provide some info to help clarify parts of this.

[squirrly]

My request for the sample model was made long ago. I’ve studied many of the models on that website in search of indepth structure and completely understand the intention of the models as example only and not to be used as created (unless one happens to hit the nail on the head!).

My project is not a business. It is a voluntary, non-profit website I developed and maintain completely alone from home, providing my local community with a means of posting lost and found pets. I’ve been doing this for about five years now. I decided to rebuild the database and website some time ago, which has been ongoing as I constantly rethink my approach to items I’ll be displaying online.

So really, my project is simple but I’ve made it challenging for myself. I recreated the model and tables long ago for current users and pets with relationships between users and pets, breeds and pet types, and so on. I’m now basically working out some minor issues with a few other relationships that need to be made........or not.

I think at this point, I’ll try a couple things and see how I do, being sure to back up my current work! I’ll check out the links you provided as well. I SO MUCH appreciate your time and your help!! If I find I’m still hung up in a few days or so, I'll be back with more info. Thank you!!

[orange]

Happy to help. Good Luck.
Call if you need something.