Recommend Table Structure

[firmusgrp]

Thanks for reading / helping.

I need a bit of guidance in creating the proper table structure for the following ... I need a table to keep "blends" which I'm calling tblBlends. A blend will be made up of one or more items from the varietal table (tblVarietal with PK varietalID).

Each blend must have at least one varietal but can have multiple, as long as the the total percentage of each sums to 100%

example:
Blend A ... 10% varietal-A 90% varietal-B
Blend B ... 100% varietal-A
etc.

This doesn't seem like a standard Parent / child table and I would appreciate some help.

Many thanks!

 

[PWise]

Blends table
Blendid pk int
Blandname varchar


BlendidvarietalTable
Blendidvarietalid pk int
Blendid
varietalID int
Percent decmail

 

[firmusgrp]

Thanks .. I left out an important piece. A blend may also include a previous blend in addition to varietals.

 

[Qik3Coder]

What's the logical difference between a blend and a varietal?
Don't get hung up in the "realities" when storing your data.

Is is logical to say that a varietal could be stored as a blend with only 1 "ingredient".

Blends
[tab]BlendID {PK}
[tab]Type {Varietal/Blend}
[tab]Description

BlendComposition
[tab]BlendCompositionID {PK}
[tab]BlendID {FK to Blends}
[tab]Percentage


HTH,
Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[firmusgrp]

@Lodlaiden

Thanks for the input. If I understand you question correctly, no.

All blends are composed of varietals (or varietals plus previous blends which are again varietals), however not all varietals ARE blends.

Basically I'm trying to track coffee blends so the blends are comprised of the different Origins (e.g. Columbian, Brazil, atc.) It gets quite a bit more complicated but I'm trying to simplify for the thread while I sort the correct schema / structure. I guess it is the possible self-referencing nature that is causing me concern.

 

[firmusgrp]

Again thanks for understanding my novice level ...
It occurs to me that my problem isn't these two tables, but the lookup table that should "sit" in between.

I'm not sure how to create a table (maybe its called a derived table), that will merge the two tables for the child lookup. Sounds like a SELECT DISTINCT sort of thing from each table creating one shared table.

I'm sure there is official language for this sort of thing.

 

[Qik3Coder]

You will need a stored procedure for that.
If you get some sample data together, someone here can probably show you how to get the data out correctly.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[firmusgrp]

Hi Lodlaiden,

Thanks. Are we talking triggers when records are added to the other two tables to "inject" an entry into a third? (and opposite for delete?)

Thx.

 

[markros]

I think you're talking about 'junction table'. Try a google search on this term.

PluralSight Learning Library

 

[Thargy]

firm,

this is really a design question, rather than SQL Server Specific.

Anyhow, to specifics, you will need a stored procedure to check upon insert that the total is 100% and enforce that. Do not use triggers, as they mean that you have a behind the scenes, undocumented side effect.

If a user invokes a stored procedure and something happens, that's fine, they knew it would. Not so with triggers.

I have seen this sort of issue before, and although the idea of a self-referencing table appeals, having compound blends consisting of other compound blends rapidly becomes a nightmare. For example you could put in a varietal with 10% of coffee A, and another varietal which contains a further 5% of the same. How would you know what the total percentage was without looking in a potentially huge hierarchy of blends within blends within blends.

The best was to keep track of it all is to boil each blend down to its simplest components, and store that. Then when you create a compound, you don't have to tree-walk all the way down the hierarchy, you just add the components. A stored procedure would do that for you readily.

You will also have to take into account rounding errors when you have odd percentages of blends, that don't sum to an integer value, and therefore don't ever sum to 100%.

Regards

T

 

[firmusgrp]

Thargy,

Thanks very much for your thoughts. I agree mostly with what you've said.

In respect to the recursion, I couldn't agree more however the business model requires it...or at least my first efforts at dissuasion failed. Luckily the amount of blends is trivial in terms of the DB overhead - at most it may reach one hundred or so (and I acknowledge how estimates of this type can be far from the reality).

In respect to the percentages specifically, luckily that property doesn't need a high degree of accuracy. They'll be put into the system by a set of sliders that I'll have manage the input values and reference each other to ensure a net 100 +/-. I was referencing the 100% enforcement more for the concept than for implementation at the DB.

Thanks very much!