A Natural Key Sequence Design

Question

Post reply

A Natural Key Sequence Design

James Malone-282392

SSC Veteran

Points: 212
More actions
January 22, 2008 at 1:39 pm

#75840

Hey, All!
I've been working in SQL Server 2005 & Oracle 10g & 11g for the past year and now I'm back at a SQL Server 2000 DB Project whose design seems straight forward, but I've a twist I'm not sure about.
Here is the basic scenario - a quality analysis data collector and reporting utility; I'm building the entire project and i'd like to be quick about it.
I think I've got a good handle on the basic structure I need, but if you notice the DB Schema w/ descriptions below, I'm wondering if there isn't a better way to at least do tbKMQATest.TestId or rather how best to implement a natual key sequence number as part of a parent key, child key combination ...
tbKMQASampleSource--- Collection points for the substances
SampleSourceId-- PK Identity Seed
SampleSourceDescription -- Test/Report Display
tbKMQAMaterialSource--- Origination of the substance (yes it is a different entity entirely from the sample source)
MaterialSourceId-- PK Identity Seed
MaterialSourceDescription -- Test/Report Display
tbKMQAMaterial--- The stuff being tested OR what its supposed to be
MaterialId-- PK Identity Seed
MaterialDescription -- Test/Report Display
tbKMQACustomer--- Orginization paying for the test(s)
CustomerId-- PK Identity Seed
CustomerDescription --Test/Report Display
tbKMQASieveSize-- Depending on the Material & Customer you will have various Sieves within your tests
SieveSizeId -- PK Identity Seed
SieveSizeDescription --Test/Report Display
tbKMQATarget --- Each Customer has material specific acceptibility ranges for each Sieve Test; Targets are time specific
TargetId-- PK
TargetMin-- Minimum Range Value
TargetMax-- Maximum Range Value
EffectiveFromDate --- What do you think?
EffectiveToDate --- What do you think?
CustomerId --- FK tbKMQACustomer
MaterialSourceId-- tbKMQAMaterialSource
SiveSizeId-- FK tbKMQASieveSize
tbKMQATestHeader--- A chosen Material goes through a SET of tests
TestHeaderID-- PK the test SET
TestYear-- Year of the test
MaterialSourceID-- Source Key
CustomerID-- Customer Key
tbKMQATest --- Each test in a set has a specific Date & Time and should be sequencial (my issue)
TestHeaderID -- PK FK
TestID -- PK SEQUENCE 1 - N for each TestHeaderID (what is the best way to acomplish this? ??)
TestDate -- Date of the Test
TestTime -- Time of the Test
tbKMQATestDetail --- Each Test will have a value for each Sieve required in its SET.
TestDetailID -- PK
TestHeaderID -- PK FK tbKMQATestHeader
TestID -- PK FK tbKMQATestSEQUENCE 1 - N for each TestHeaderID
SieveSizeId -- PK FK tbKMQASieveSize
SieveValue -- Result of the individual Sieve Test

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Terry-239890 Ten Centuries Points: 1387 More actions · Answer 1

Hi James

I have spent some time to figure out what your environment is focused on table association. This is what I discovered.

1. tbKMQATarget has 3 FKs(FK tbKMQACustomer, FK tbKMQAMaterialSource, FK tbKMQASieveSize).

2. tbKMQATestHeader has 2 FKs(FK tbKMQACustomer, FK tbKMQAMaterialSource).

3. tbKMQATestDetail has 3 FKs but one of them is FK tbKMQASieveSize.

Then tbKMQATarget could have 1 to 1 relation to tbKMQATestDetail. Please correct me if I am wrong.

Therefore the table tbKMQATestDetail may have a FK to tbKMQATestDetail and you can remove 2 FKs(FK tbKMQACustomer, FK tbKMQAMaterialSource) which makes a lot simpler to understand what you are trying to achieve.

Going back to your question, about parent-child associated compound key, if you have reporting requirement to display MaterialID and CustomerID(instead of descriptions i mean) you can create compound key. But otherwise I suggest you to create a new identity for each table for tbKMQATest and tbKMQATestDetail table as below:

tbKMQATest

TestID -- PK Identity

TestHeaderID -- FK

SequenceNo-- SEQUENCE 1 - N for each TestHeaderID

TestDate -- Date of the Test

TestTime -- Time of the Test

tbKMQATestDetail

TestDetailID -- PK Identity

TestID -- FK tbKMQATest

SieveSizeId -- PK FK tbKMQASieveSize

SieveValue -- Result of the individual Sieve Test

And this is something you may have to consider to change the FK.

tbKMQATarget

TargetId-- PK

TargetMin-- Minimum Range Value

TargetMax-- Maximum Range Value

EffectiveFromDate ---

EffectiveToDate ---

TestDetailID-- FK tbKMQATestDetail

/*remove the following compound key

CustomerId --- FK tbKMQACustomer

MaterialSourceId-- tbKMQAMaterialSource

SiveSizeId-- FK tbKMQASieveSize

*/

Just a friendly discussion...

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

James,

Are you expecting the TestID to repeat, starting at 1, for each HeaderID in the test table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

James Malone-282392 SSC Veteran Points: 212 More actions · Answer 3

Yes, that is how I will need to write the application - regardless of wheather I am able to implement it within the DB Schema or Application Business Logic;

Thanks

James Malone-282392 SSC Veteran Points: 212 More actions · Answer 4

Thank you for the discussion; one of my problems is that the targets exist without respect to the test and are applied to a test based on the tests' customer specifications for each sieve per material and material source; I did intend to explain this well enough not to cause anyone to loop the test backwards as the parent of the target, sorry for that.

It may be that trying to constrain this application logic at the db schema level isn't practical, but I'd like to get as concrete a footing as possible before attempting to script the business logic.

Let me know what you think,

Thanks