I'm doing a site for a cat breeder, and I'm having some trouble coming up with a good structure for the MySQL database (what tables to use and how to link them together, especially how to link them together).

I've come up with a sketch of how I think it should work. I'll try to explain so that everyone understand
I'll connect different things with "lines" (using - and |).

----- cats - all info about a cat
|---- litters - litters born
|   |
|---|--- parents - parents to the litters ----------|
|---|--- kittens - kittens in the litters ----------|
|                                                   |
|---- our_cats - cats owned by the breeder ---------|
    |--- shows - results from cat shows

Basically there is a table cats, which hold all information about ALL cats. litters is a seperate table (not linked with cats), which hold information about born litters. In every litter there is kittens and parents, with attributes that links them to a litter (or to several litters for parents). But kittens and parents is also linked with the cats table, which hold information about them.

our_cats is also linked with the cats table, all the breeders cats is in the cats table as well. But our_cats may also be linked with parents and kittens, because the breeders cats can become parents, and the breeder can keep some of the kittens as their own.

Then finally there is the shows table, which holds show results. It is linked to our_cats, because the breeder only take their own cats to shows.


This is as far as I get. I need help to implement this (or in some other way if someone can come up with something smarter) and to make the tables.

Thanks
lizciz

I've finally managed to get it together. Since this post didn't seem so interesting I won't bother you with my solution :P

However, I have a new question.
In a 1-to-many relationship, how can I select the '1' and all the 'many'? Can it be done in a single SQL query? I'll try to be really clear in my explanation.

I have news on my website, stored in a 'News' table. Thre is also a 'Images' table for images, and finally a 'NewsImages' table, which connects images to their respective news. Every news can have 0-5 images.

I have already written classes for news and images. The image objects are stored in an array within the news object and both classes has a method for printing it out to the page.

So in my query I need to select some news, together with it's images and parse it into a news object (I already have a method for that), and the info from the image table is used to parse image objects. But at this point, if there is, lets say, 3 images connected to these news how can I parse 3 different image objects?

Thanks
lizciz