Normalization - Avoiding Null Values

[toddaxsom]

I need help understanding the best solution in normalizing a database that records both purebreed and hybrid dog information without using null values. Both types will have info that relates to all dog breeds, which works great for a "breeds" table. However, hybrids have unique attributes, as do purebreeds.



Would it be better to have a "hybrid" table and a "purebreeds" table, rather than a "breeds" table? If, they are split tables, how do I filter them into an "all breeds" table without having duplicate primary keys that are auto numbering?

I do already have junction tables for several attributes. But, every option I have tried, ends up having to allow for null values, which I'm trying hard to avoid.

If this sounds confusing, I totally understand! Any help is appreciated.

[NTC]

An auto number field will never create a duplicate value and a primary key field will not allow one.

Whether or not pure and hybrid should be separate tables or all in the same table I can't say because I really don't know the topic - - but if I were to draw an analogy to autos and trucks - - I would tend to say put them all into one table: vehicles.

If you make them 2 separate tables there is no simple method to merge them into 1 record set (table) - one would need to append them into a common temp table. If this is a need then it is more likely you are better off having all the data in 1 table.

[toddaxsom]

If I have a single table called "breeds" with an id of "breed ID", how do I show that hybrids are a mix of breed 1, breed 2, breed 3?

The primary keys for all the breeds are already listed in the "breeds" table.

[davegri]

How about a Breeds table and an Attributes table? All dogs go in Breeds, both purebred and hybrid.
The Attributes table could contain things like 'Hound' or even 'German Shorthair Pointer' so that you could include any number of attributes including purebred attributes for a given entry in the Breeds table.
UpRider

[NTC]

again - what makes sense, as the best design - requires some more knowledge of your topic than I have.

A reasonable single table approach could have Mix1, Mix2, etc fields to enter those pure breed IDs. And within maybe a limit of ~5 or so pure breed fields this typically is very do-able.

If however hybrids can have a mix of lots of breeds - dozens or more then the design is better of being a table for Breeds and a Master Table for Hybrids (of the hybrid's common data - 1 record per hybrid) and a Sub Table for Hybrid breeds that links to the Hybrid table. This allows a flexible and unlimited number of pure breeds to be assigned to any single Hybrid.