1:1 Relationship? 1

[DanJR]

My question is: should a physical one to one realtionship exist in my design...and should the tblFormulations table exist at all....

I have a small access database to store information for a food technologist in a company. Every now and then, the food technie is given a project (idea) to develop for a client. The project consists of creating a number of samples until the client either terminates the project or one of the samples is accepted and put into production (product). Both products and samples have a number of ingredients (called a formulation). A sample must have exactly one formulation but each sample can be made into more than one product (different sizes). Therefore , there is a 1:1 on Samples and Formulation - which could be absorbed into one table.

However, historical product information may/will be imported into the db, therefore many projects with formulations will not be associated with a sample (or a project). Secondly, samples are involved in other relationships that do not apply to products.

At the moment I have a somewhat strange design (non-relevant field obmitted):
[tt]
tblSamples:
-FormulationID (PK)
-ProjectID

tblFormulations:
-FormulationID (PK)

tblFormulationItems:
-IngredName (CK)
-FormulationID (CK)

tblProducts:
-ProductCode
-FormulationID
[/tt]
Or, should I store the Formulation Items (ingredients) for products and Samples in separate tables. If I do this, then it will be possible to match up a Product to a Sample which has a different mix of ingredients(!).

Any thoughts on how I should relate samples to projects (and their associated formulation).

I'm not sure if by brain is still on the same planet...

Cheers,
Dan

 

[LittleSmudge]

tblSamples
FormulationID (PK)
-ProjectID If this is an ID then is it a joint PK ?

tblFormulations
FormulationID (PK)

tblFormulationItems
-IngredName (CK) What is a CK ? Do you mean Foreign Key - FK ? Is this then a joint PK as well ( A linking table )
-FormulationID (CK) ditto

tblProducts
-ProductCode
-FormulationID Is this the PK - as it is an Id ?





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[DanJR]

Sorry for the confusion...
[tt]
tblSamples
FormulationID (PK)
-ProjectID (FK - from Project table, not shown)

tblFormulations
FormulationID (PK)

tblFormulationItems
-IngredName (PK
-FormulationID (PK

tblProducts
-ProductCode (PK)
-FormulationID (FK - from tblFormulations)
[/tt]

1. Samples and Products both have formulations (a mix of ingredients)
2. A Sample can be made into more than one Product
3. Both a Sample and Product must have a formulation
4. A Product does not always have an associated sample.

Cheers,
Dan

 

[Darrylles]

Hi Dan,

If I understand you correctly, then this is how I would do it...
[tt]
Project (1) -------> (M) Sample
Sample (1) -------> (M) Formulation
Formulation (1) -------> (M) Form_Item

Project
P_PK (PK)

Sample
S_PK (PK)
P_FK (FK) 'to Project
Product (Y/N) 'Is this a product?

Formulation
F_PK (PK)
S_FK (FK) 'to Sample

Form_Item
I_PK (PK)
F_FK (FK) 'to Formulation
[/tt]
As a PRODUCT is simply a SAMPLE that has been accepted, then why not have a boolean Product field in the SAMPLE table to indicate whether this SAMPLE is in fact a Product?
When a Sample becomes a Product, then this Sample record becomes useless anyway no?

Remember that 1:M means 1 to zero OR 1 to 1 OR 1 to Many.
You can disallow a new formulation record if one already
exists.

"However, historical product information may/will be imported into the db, therefore many projects with formulations will not be associated with a sample (or a project). Secondly, samples are involved in other relationships that do not apply to products."

I assume there is a typo in the above: should 'projects with formulations' be 'products with formulations'?
If so, then the old Product + Formulation would become Sample + Formulation with the Product field in Sample being set to True.

Secondly, you can create a Sample record totally unrelated to any other table if you wish.

Hope this is somewhere near the mark.

Regards

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[DanJR]

Hi Darrylles,

Thanks heaps for your input and yes, you're correct about the typo. The only problem with using a boolean field to indicate that the sample is a product is that samples can be made into more than one product (1:M). Most of the attributes of a Sample are different from Product attributes.

I've also thought about a slightly different design, where each sample and product have their own formulation. Thus, if two products are derived from the same sample, the formulations will be duplicated in the tblFormulations table. E.g. the formulations would like something like:

FormulationID ProductID SampleID
1 - 1
2 1 -
3 2 -
4 - 2

A Product could be linked to a Sample with a reference to the Sample in the Products table. This would made building forms and searching easy, and I could use code to enforce whether or not a product can be linked to a Sample. This method creates duplication/redundancy in formulations and of course potential anomalies.

I also see other ways, like including a SampleID field the Formulations table and having a 1:M between Formulations and Products.

Cheers,
Dan

 

[Darrylles]

Hi Dan,

Something a little different:

Formulation IS absorbed into Sample table (as you first envisaged).
The Product key is the combined Project_PK + Sample_PK.
So Product has Project_FK and Sample_FK and BOTH are it's primary key.

Project
|
1|
/ / / / / M/ \M
Sample ------- Product
1| 1 M
|
|
M|
Form_Item


The above graphically displays this....
Project has many Samples.
Project has many Products. (Because a sample within a Project can be used by many Products).
Sample has many Formulation Items.
A Product has only one Sample and only one Project.

This cuts out duplication and is enforced by Access through relationships.

The only real change is the combined PK in Product.

Have I missed anything Dan?

Regards,

Darrylle


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Darrylles]

Sorry - forgot formatting...

Something a little different:

Formulation IS absorbed into Sample table (as you first envisaged).
The Product key is the combined Project_PK + Sample_PK.
So Product has Project_FK and Sample_FK and BOTH are it's primary key.
[tt]
Project
|
1|
/ / / / / M/ \M
Sample ------- Product
1| 1 M
|
|
M|
Form_Item
[/tt]

The above graphically displays this....
Project has many Samples.
Project has many Products. (Because a sample within a Project can be used by many Products).
Sample has many Formulation Items.
A Product has only one Sample and only one Project.

This cuts out duplication and is enforced by Access through relationships.

The only real change is the combined PK in Product.

Have I missed anything Dan?

Regards,

Darrylle
"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[DanJR]

Hi Darrylles,

Thanks heaps for all your ideas. I'll give this a go. You've been extremely helpfull...


Thanks
Dan