Hi all,

Am somewhat new to SQL (although have been programming for 2 years), however hopefully someone can assist with a question or two I have (or point me in the right direction resource wise) in creating table relationships.

1. I have, as an example, the following 4 tables:
Purchase
purchaseID (PK)
Rel: Many to 1 with Budget

Budget
purchaseID (PK)
speciesID (PK/FK)
Rel: Many to 1 with Species
Rel: One to Many with Purchase

Species
speciesID
speciesType
Rel: Has a 1 to Many with Breed
Rel: Has a 1 to Many with Budget

Breed
breedID (PK)
breedType
Rel: Has a Many to 1 with Species

Typically the data in 'Species' and 'Breed' are primarily for reference, and Budget and Purchase are used to record Purchases of Species being of a Breed type. The 'Breed' table is like a subclass of 'Species', with Species Having numerous types with each having numerous Breed types as well.

Now If I want to store the Breed Type from a selected Species for a Purchase (which is selected from a drop down list directly referencing the Brreed Table) do I:
(a). Create a field in the 'Purchase' table and simply populate it from the user selection.
(b). Created a relationship in the DB between the Purchase Table and the Breed Table so as to inherit the breedID value as a Foreign Key which would map, on being queried, the the BreedType.

(a) doesnt seem right as It feels like I'm replicating data, yet if I applied (b) to all tables in the DB they would all be linked! How far deep in relationship, going through other tables, do you go to retrieve a value before you just relate two tables directly?

TIA

In my view a more natural relationships would be from "Purchase" to "Breed" to "Species", which would also make "Budget" redundant (unless there are some other attributes that you've not shown), because the information could be derived from a join of the other three tables.

I don't understand the Budget table either. What is its purpose?

Sorry, should of explained some more as the Budget table is giving me issues with my design.
A Purchase details an order by the customer (being of Species type of a particular Breed Type)
A Budget identifies to the supply company the costs it incurs in supplying (Supply) Species.
A Budget is not per customer as the act of supplying Species can cover many Purchases, the supply company categorize a Budget per supply on Species type.
Hence a Supply will have enough Species qty to cover all Purchases. And a Supply will have a Budget per Species type.

That explanation made my head hurt. Here is my best guess as to what you need. In this layout Budget really isn't related to Purchase directly, it is only related via species which is determined via Breed:

Species
speciesID
speciesType

Breed
breedID (PK)
speciesID (FK to Species)
breedType

Purchase
purchaseID (PK)
breedID (FK to Breed)

Budget
speciesID (FK to Species)
CostPer
BudgetQty

Thats my best guess. If it isn't close then you will have to organize your thoughts a bit better and try another explanation. Or maybe somebody else here will understand what you have already said.

unless you have multiple suppliers what does the budget table indicate?
it looks like a redundant table to me, its not storing anything that couldn't be stored in the species table
if it contained data from multiple suppliers, or differnt data over time I could see its use, but as described so far it doesn't look right

in any event I'd expect that sort of infroamtuon to be on the breed level, not species level unless all breeds cost the same.

take ferisntance dogs
the cost of dog depends on lots of factors, the physical size, age, quality of pedigree and so on. so I don't think l you could model one budget price for all breeds of dog, and I doubt you will be able to do that for almost any other such species/breed combinations. however I'm sure there are soem, but that may be down to language.