A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Will my table design cause future problems



 
 
Thread Tools Display Modes
  #1  
Old April 27th, 2004, 11:11 PM
Aaron
external usenet poster
 
Posts: n/a
Default Will my table design cause future problems

Access/VBA newbie here and I have the following situation:
A research facility mixes elements(LotID), processes them,
evaluates the end product and then uses it in a part which
will also get tested.
The table structures I have so far:
tblComposition (created this way because a Lot# can be
made up of 1-? elements)
LotID
Element
ElementRatio

tblProcessing
LOtID
ProcessEquip
RunDate
%Source

tblEvaluation
LotID
ParticleSize
ECValue
FinalMAss

tblParts
Part#
LotID
Account
Performance

One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of Al,Si,Cu at
a ratio of 60,20,20 and was used in PART#1234,PART#5678,etc
If I continue down this path I will need to concatenate
the records in the tblComposition and I don't know how big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.
Ads
  #2  
Old April 28th, 2004, 06:58 AM
TC
external usenet poster
 
Posts: n/a
Default Will my table design cause future problems

Aaron, you will get nowhere with this question until you identify what is
the "primary key" of each table, and state exactly what thing or event is
identified by each row in the table.

For example:

tblClient - one row for each client known to the system.
ClientID (PK)
Forname
Surname
etc.

This might help:
http://support.microsoft.com/support...es/Q100139.ASP

HTH,
TC
(off for the day)


"Aaron" wrote in message
...
Access/VBA newbie here and I have the following situation:
A research facility mixes elements(LotID), processes them,
evaluates the end product and then uses it in a part which
will also get tested.
The table structures I have so far:
tblComposition (created this way because a Lot# can be
made up of 1-? elements)
LotID
Element
ElementRatio

tblProcessing
LOtID
ProcessEquip
RunDate
%Source

tblEvaluation
LotID
ParticleSize
ECValue
FinalMAss

tblParts
Part#
LotID
Account
Performance

One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of Al,Si,Cu at
a ratio of 60,20,20 and was used in PART#1234,PART#5678,etc
If I continue down this path I will need to concatenate
the records in the tblComposition and I don't know how big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.



  #3  
Old April 28th, 2004, 05:57 PM
Aaron
external usenet poster
 
Posts: n/a
Default Will my table design cause future problems

I added some key information, hopefully this along with
other descriptions helps. thanks.
-----Original Message-----
Aaron, you will get nowhere with this question until you

identify what is
the "primary key" of each table, and state exactly what

thing or event is
identified by each row in the table.

For example:

tblClient - one row for each client known to the system.
ClientID (PK)
Forname
Surname
etc.

This might help:
http://support.microsoft.com/support...cles/Q100139.A

SP

HTH,
TC
(off for the day)


"Aaron" wrote in

message
...
Access/VBA newbie here and I have the following

situation:
A research facility mixes elements(LotID), processes

them,
evaluates the end product and then uses it in a part

which
will also get tested.
The table structures I have so far:
tblComposition (Lot# can be made up of 1-? elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)

tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source

tblEvaluation (characterization of LotID's after mfg)
LotID (FK to tblManufacturing,no duplicates)
ParticleSize
ECValue
FinalMAss

tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)

One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of Al,Si,Cu

at
a ratio of 60,20,20 and was used in

PART#1234,PART#5678,etc
If I continue down this path I will need to concatenate
the records in the tblComposition and I don't know how

big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.



.

  #4  
Old April 29th, 2004, 04:07 AM
TC
external usenet poster
 
Posts: n/a
Default Will my table design cause future problems


"Aaron" wrote in message
...

(snipped)

A research facility mixes elements(LotID), processes them,
evaluates the end product and then uses it in a part which
will also get tested.


So:
- each part has one (or several?) tests;
- each part comprises several end products;
- each end product comprises several elements.
Correct?


tblComposition (Lot# can be made up of 1-? elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)


What is the primary key of that table? What does one row in that table,
represent?



tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source


What does one row in that table, represent?


tblEvaluation (characterization of LotID's after mfg)
LotID (FK to tblManufacturing,no duplicates)
ParticleSize
ECValue
FinalMAss


What is the primary key of that table? What does one row in that table,
represent? (I don't understand: "characterization of LotID's after
manufacturing". You need to describe the table in terms of the entitites you
have mentioned so far: parts, tests, elements, end products, etc.)


tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)


Aaron, that is the only table that is clearly described so far. Each row in
that table represents a single, unique Part. Each part is identified by a
unique Part#. Part# is the primary key of that table. We need the same level
of understanding of your other tables, before we can comment.

HTH,
TC



One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of Al,Si,Cu at
a ratio of 60,20,20 and was used in PART#1234,PART#5678,etc
If I continue down this path I will need to concatenate
the records in the tblComposition and I don't know how big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.



  #5  
Old April 29th, 2004, 04:04 PM
Aaron
external usenet poster
 
Posts: n/a
Default Will my table design cause future problems


-----Original Message-----

"Aaron" wrote in

message
...

(snipped)

A research facility mixes elements(LotID), processes

them,
evaluates the end product and then uses it in a part

which
will also get tested.


So:
- each part has one (or several?) tests? several types of

tests
- each part comprises several end products? The combined

elements(LotID) can be used in several Part#'s. Each Part#
uses up to 2 LotID's(Front and Back) but the Front LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?


tblComposition (Lot# can be made up of 1-? elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)


What is the primary key of that table? Actually I have a

autonumber CompID PK as well as a multi-field index
between LotID and Element.
What does one row in that table, represent?
Row1: Auto#,LotID112233,Al,60
Row2: Auto#,LotID112233,Cu,40
The concatenated result would be LotID112233,AlCu,60:40



tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source
SourceID

What does one row in that table, represent? The combined

elements from above are mixed with a source element to
produce the final combination which will be used in a part.


tblEvaluation (characterization of LotID's after mfg)
LotID (PK)one-one relationship to tblMAnufacturing
ParticleSize
ECValue
FinalMAss


What is the primary key of that table? LotID is actually

the PK
What does one row in that table,represent? (I don't
understand: "characterization of LotID's after
manufacturing". You need to describe the table in terms of
the entitites you
have mentioned so far: parts, tests, elements, end

products, etc.) The combined/processed elements are tested
(characterized) before being used in Parts. Each row will
contain the results for a LotID


tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)


Aaron, that is the only table that is clearly described

so far. Each row in
that table represents a single, unique Part. Each part is

identified by a
unique Part#. Part# is the primary key of that table. We

need the same level
of understanding of your other tables, before we can

comment.

HTH,
TC



One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of

Al,Si,Cu at
a ratio of 60,20,20 and was used in

PART#1234,PART#5678,etc
If I continue down this path I will need to

concatenate
the records in the tblComposition and I don't know

how big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.



.

  #6  
Old April 30th, 2004, 03:17 AM
TC
external usenet poster
 
Posts: n/a
Default Will my table design cause future problems

Hi AAron

Sorry, but I still don't clearly understand the data that you're dealing
with.

Let's get the data descriptions correct, before we proceed. This may seem
picky, but it is actually the most important step in "data modelling". A
clear understanding of the data entities must come first - then the actual
design of the tables.

Is this correct:

- The research facility handles ELEMENTS, END PRODUCTS, and PARTS.

- I assume that each ELEMENT has a unique ELEMENTID? If not, how do you
distinguish different ELEMENTs?

- Two or more ELEMENTS are mixed together. The result is called a LOT. Each
LOT has a unique LOTID. There is no case where two LOTs have the same LOTID.

- Each LOT is PROCESSED to form an END-PRODUCT.

- Is there only one LOT per END PRODUCT, or could you process several LOTs
into a sinbgle END PRODUCT?

- I assume that each END PRODUCT has an ENDPRODUCTID? If not, how do you
distinguish different END PRODUCTs?

- Each END PRODUCT is subject to one or more TESTS.

- I assume that each TEST has a unique TESTID. If not, how do you
distinguish different TESTSs?

- Could one TEST handle several END PRODUCTs simultaneously?

Aaron, you may feel I am going backwards with my advice to you! (We do not
seem to be getting down into the nitty gritty of that table structures, at
all.) But the reason for this, is that you have not yet clearly described
the data that you need to manage. So IMO, it is premature to comment on the
table structures.

Unfortunately I'll be busy for the next 2 days, & have little or no time on
the net. But I'm happy to get back to this as soon as I return. Or maybe
someone else will jump in & help.

Cheers,
TC


A research facility mixes elements(LotID), processes

them,
evaluates the end product and then uses it in a part

which
will also get tested.


So:
- each part has one (or several?) tests? several types of

tests
- each part comprises several end products? The combined

elements(LotID) can be used in several Part#'s. Each Part#
uses up to 2 LotID's(Front and Back) but the Front LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?




"Aaron" wrote in message
...

-----Original Message-----

"Aaron" wrote in

message
...

(snipped)

A research facility mixes elements(LotID), processes

them,
evaluates the end product and then uses it in a part

which
will also get tested.


So:
- each part has one (or several?) tests? several types of

tests
- each part comprises several end products? The combined

elements(LotID) can be used in several Part#'s. Each Part#
uses up to 2 LotID's(Front and Back) but the Front LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?


tblComposition (Lot# can be made up of 1-? elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)


What is the primary key of that table? Actually I have a

autonumber CompID PK as well as a multi-field index
between LotID and Element.
What does one row in that table, represent?
Row1: Auto#,LotID112233,Al,60
Row2: Auto#,LotID112233,Cu,40
The concatenated result would be LotID112233,AlCu,60:40



tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source
SourceID

What does one row in that table, represent? The combined

elements from above are mixed with a source element to
produce the final combination which will be used in a part.


tblEvaluation (characterization of LotID's after mfg)
LotID (PK)one-one relationship to tblMAnufacturing
ParticleSize
ECValue
FinalMAss


What is the primary key of that table? LotID is actually

the PK
What does one row in that table,represent? (I don't
understand: "characterization of LotID's after
manufacturing". You need to describe the table in terms of
the entitites you
have mentioned so far: parts, tests, elements, end

products, etc.) The combined/processed elements are tested
(characterized) before being used in Parts. Each row will
contain the results for a LotID


tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)


Aaron, that is the only table that is clearly described

so far. Each row in
that table represents a single, unique Part. Each part is

identified by a
unique Part#. Part# is the primary key of that table. We

need the same level
of understanding of your other tables, before we can

comment.

HTH,
TC



One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of

Al,Si,Cu at
a ratio of 60,20,20 and was used in

PART#1234,PART#5678,etc
If I continue down this path I will need to

concatenate
the records in the tblComposition and I don't know

how big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.



.



  #7  
Old April 30th, 2004, 06:55 PM
Aaron
external usenet poster
 
Posts: n/a
Default Will my table design cause future problems


-----Original Message (edited w/reply*)-----
Hi AAron

Sorry, but I still don't clearly understand the data that

you're dealing
with.

Let's get the data descriptions correct, before we

proceed. This may seem
picky, but it is actually the most important step

in "data modelling". A
clear understanding of the data entities must come first -

then the actual
design of the tables.

Is this correct:

- The research facility handles ELEMENTS, END PRODUCTS,

and PARTS.

- I assume that each ELEMENT has a unique ELEMENTID? If

not, how do you
distinguish different ELEMENTs? *Yes I have a table of

Elements: Autonumber ID and ElementName

- Two or more ELEMENTS are mixed together. The result is

called a LOT. Each
LOT has a unique LOTID. There is no case where two LOTs

have the same LOTID. *Correct

- Each LOT is PROCESSED to form an END-PRODUCT.

- Is there only one LOT per END PRODUCT, or could you

process several LOTs
into a sinbgle END PRODUCT? *One lot per END PRODUCT

- I assume that each END PRODUCT has an ENDPRODUCTID? If

not, how do you
distinguish different END PRODUCTs? *The LOTID assigned

at element composition is the same as the ID for the END
PRODUCT. The reason for the different table is because
since it can have more than 1 element and I need to
capture the amount/ratio of each Element used, I thought
it best to have the separate table. If you have another
suggestion please let me know.

- Each END PRODUCT is subject to one or more TESTS.

- I assume that each TEST has a unique TESTID. If not,

how do you
distinguish different TESTSs? *"Tests" are unique fields

such as ParticleSize, ECvalue, FinalMass. Again, it's more
of a characterization of the mixture of elements.

- Could one TEST handle several END PRODUCTs

simultaneously? *The Products are tested/characterized
independently but they all receive the same "tests".

Aaron, you may feel I am going backwards with my advice

to you! (We do not
seem to be getting down into the nitty gritty of that

table structures, at
all.) But the reason for this, is that you have not yet

clearly described
the data that you need to manage. So IMO, it is premature

to comment on the
table structures.

Unfortunately I'll be busy for the next 2 days, & have

little or no time on
the net. But I'm happy to get back to this as soon as I

return. Or maybe
someone else will jump in & help.
Cheers,
TC

*I appreciate the assistance thus far and I understand you
need to know the data.
--------------------------------------------------------

A research facility mixes elements(LotID),

processes
them,
evaluates the end product and then uses it in a

part
which
will also get tested.

So:
- each part has one (or several?) tests? several types

of
tests
- each part comprises several end products? The

combined
elements(LotID) can be used in several Part#'s. Each

Part#
uses up to 2 LotID's(Front and Back) but the Front LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?




"Aaron" wrote in

message
...

-----Original Message-----

"Aaron" wrote in

message
...

(snipped)

A research facility mixes elements(LotID),

processes
them,
evaluates the end product and then uses it in a

part
which
will also get tested.

So:
- each part has one (or several?) tests? several types

of
tests
- each part comprises several end products? The

combined
elements(LotID) can be used in several Part#'s. Each

Part#
uses up to 2 LotID's(Front and Back) but the Front LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?


tblComposition (Lot# can be made up of 1-?

elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)

What is the primary key of that table? Actually I have

a
autonumber CompID PK as well as a multi-field index
between LotID and Element.
What does one row in that table, represent?
Row1: Auto#,LotID112233,Al,60
Row2: Auto#,LotID112233,Cu,40
The concatenated result would be LotID112233,AlCu,60:40



tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source
SourceID
What does one row in that table, represent? The

combined
elements from above are mixed with a source element to
produce the final combination which will be used in a

part.


tblEvaluation (characterization of LotID's after

mfg)
LotID (PK)one-one relationship to tblMAnufacturing
ParticleSize
ECValue
FinalMAss

What is the primary key of that table? LotID is

actually
the PK
What does one row in that table,represent? (I don't
understand: "characterization of LotID's after
manufacturing". You need to describe the table in terms

of
the entitites you
have mentioned so far: parts, tests, elements, end

products, etc.) The combined/processed elements are

tested
(characterized) before being used in Parts. Each row

will
contain the results for a LotID


tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)

Aaron, that is the only table that is clearly described

so far. Each row in
that table represents a single, unique Part. Each part

is
identified by a
unique Part#. Part# is the primary key of that table.

We
need the same level
of understanding of your other tables, before we can

comment.

HTH,
TC



One of the key deliverables from this database is

to
provide Evaluation data and Part listings for

every
combination of Elements and %Source information.

i.e;
LOT#AALL12 had a 10%Source and was made up of

Al,Si,Cu at
a ratio of 60,20,20 and was used in

PART#1234,PART#5678,etc
If I continue down this path I will need to

concatenate
the records in the tblComposition and I don't know

how big
a problem that will be. I also don't know what

other
problems I am not considering. Please advise.

Thanks.


.



.

  #8  
Old May 3rd, 2004, 06:03 AM
TC
external usenet poster
 
Posts: n/a
Default Will my table design cause future problems

Ok. Now we have the following clear description of your needs:

"The research facility handles ELEMENTS, LOTS, END PRODUCTS and TESTS. Two
or more ELEMENTS are mixed together, in various amounts or proportions, into
a LOT. Each LOT is PROCESSED to form an END-PRODUCT. (There is just one LOT
per END PRODUCT.) The END PRODUCT is then subject to various TESTS."

Here is the correct table structure to meet those needs (excluding TESTS).
Obviously your own table & field names may vary from mine - but there should
be a 1-to-1 correspondence between your tables, and these:

tblElement - one row for each ELEMENT known to the system.
ElementID (PK)
ElementName
- any other attributes of the element as a whole; eg. toxicity.

tblLot - one row for each LOT known to the system.
LotID (PK)
Processed - a yes/no flag to indicate whether this LOT has been
processed into an END PRODUCT yet.
- any other attributes of the lot as a whole; eg. date created.

tblLotElement - one row for each ELEMENT in each LOT.
LotID ( composite )
ElementID ( primary key )
- a field to give the amount or proportion of that ELEMENT in that LOT.

I haven't included TESTS, because I still do not understand your description
of those.

Aaron, I hope this helps. Note that I designed the table structures above,
in about 1 minute (literally), once I understood your data requirements.
That first "understanding" stage, is where most of the time lies. Do not
try to design the tables during that stage. The trick is to get an accurate
understanding of the data requirements, before you start designing the
tables.

HTH,
TC


"Aaron" wrote in message
...

-----Original Message (edited w/reply*)-----
Hi AAron

Sorry, but I still don't clearly understand the data that

you're dealing
with.

Let's get the data descriptions correct, before we

proceed. This may seem
picky, but it is actually the most important step

in "data modelling". A
clear understanding of the data entities must come first -

then the actual
design of the tables.

Is this correct:

- The research facility handles ELEMENTS, END PRODUCTS,

and PARTS.

- I assume that each ELEMENT has a unique ELEMENTID? If

not, how do you
distinguish different ELEMENTs? *Yes I have a table of

Elements: Autonumber ID and ElementName

- Two or more ELEMENTS are mixed together. The result is

called a LOT. Each
LOT has a unique LOTID. There is no case where two LOTs

have the same LOTID. *Correct

- Each LOT is PROCESSED to form an END-PRODUCT.

- Is there only one LOT per END PRODUCT, or could you

process several LOTs
into a sinbgle END PRODUCT? *One lot per END PRODUCT

- I assume that each END PRODUCT has an ENDPRODUCTID? If

not, how do you
distinguish different END PRODUCTs? *The LOTID assigned

at element composition is the same as the ID for the END
PRODUCT. The reason for the different table is because
since it can have more than 1 element and I need to
capture the amount/ratio of each Element used, I thought
it best to have the separate table. If you have another
suggestion please let me know.

- Each END PRODUCT is subject to one or more TESTS.

- I assume that each TEST has a unique TESTID. If not,

how do you
distinguish different TESTSs? *"Tests" are unique fields

such as ParticleSize, ECvalue, FinalMass. Again, it's more
of a characterization of the mixture of elements.

- Could one TEST handle several END PRODUCTs

simultaneously? *The Products are tested/characterized
independently but they all receive the same "tests".

Aaron, you may feel I am going backwards with my advice

to you! (We do not
seem to be getting down into the nitty gritty of that

table structures, at
all.) But the reason for this, is that you have not yet

clearly described
the data that you need to manage. So IMO, it is premature

to comment on the
table structures.

Unfortunately I'll be busy for the next 2 days, & have

little or no time on
the net. But I'm happy to get back to this as soon as I

return. Or maybe
someone else will jump in & help.
Cheers,
TC

*I appreciate the assistance thus far and I understand you
need to know the data.
--------------------------------------------------------

A research facility mixes elements(LotID),

processes
them,
evaluates the end product and then uses it in a

part
which
will also get tested.

So:
- each part has one (or several?) tests? several types

of
tests
- each part comprises several end products? The

combined
elements(LotID) can be used in several Part#'s. Each

Part#
uses up to 2 LotID's(Front and Back) but the Front LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?




"Aaron" wrote in

message
...

-----Original Message-----

"Aaron" wrote in
message
...

(snipped)

A research facility mixes elements(LotID),

processes
them,
evaluates the end product and then uses it in a

part
which
will also get tested.

So:
- each part has one (or several?) tests? several types

of
tests
- each part comprises several end products? The

combined
elements(LotID) can be used in several Part#'s. Each

Part#
uses up to 2 LotID's(Front and Back) but the Front LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?


tblComposition (Lot# can be made up of 1-?

elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)

What is the primary key of that table? Actually I have

a
autonumber CompID PK as well as a multi-field index
between LotID and Element.
What does one row in that table, represent?
Row1: Auto#,LotID112233,Al,60
Row2: Auto#,LotID112233,Cu,40
The concatenated result would be LotID112233,AlCu,60:40



tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source
SourceID
What does one row in that table, represent? The

combined
elements from above are mixed with a source element to
produce the final combination which will be used in a

part.


tblEvaluation (characterization of LotID's after

mfg)
LotID (PK)one-one relationship to tblMAnufacturing
ParticleSize
ECValue
FinalMAss

What is the primary key of that table? LotID is

actually
the PK
What does one row in that table,represent? (I don't
understand: "characterization of LotID's after
manufacturing". You need to describe the table in terms

of
the entitites you
have mentioned so far: parts, tests, elements, end
products, etc.) The combined/processed elements are

tested
(characterized) before being used in Parts. Each row

will
contain the results for a LotID


tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)

Aaron, that is the only table that is clearly described
so far. Each row in
that table represents a single, unique Part. Each part

is
identified by a
unique Part#. Part# is the primary key of that table.

We
need the same level
of understanding of your other tables, before we can
comment.

HTH,
TC



One of the key deliverables from this database is

to
provide Evaluation data and Part listings for

every
combination of Elements and %Source information.

i.e;
LOT#AALL12 had a 10%Source and was made up of
Al,Si,Cu at
a ratio of 60,20,20 and was used in
PART#1234,PART#5678,etc
If I continue down this path I will need to
concatenate
the records in the tblComposition and I don't know
how big
a problem that will be. I also don't know what

other
problems I am not considering. Please advise.

Thanks.


.



.



  #9  
Old May 4th, 2004, 12:03 AM
Aaron
external usenet poster
 
Posts: n/a
Default Will my table design cause future problems

Thanks again. So far your tables line up with what I was
thinking of doing. I'm thinking now that the TEST fields
could just be in tblLot instead of a separate table
because they are characteristics of LotID such as
ElectricalPerformance, AvgParticleSize, etc. ????

-----Original Message-----
Ok. Now we have the following clear description of your

needs:

"The research facility handles ELEMENTS, LOTS, END

PRODUCTS and TESTS. Two
or more ELEMENTS are mixed together, in various amounts

or proportions, into
a LOT. Each LOT is PROCESSED to form an END-PRODUCT.

(There is just one LOT
per END PRODUCT.) The END PRODUCT is then subject to

various TESTS."

Here is the correct table structure to meet those needs

(excluding TESTS).
Obviously your own table & field names may vary from

mine - but there should
be a 1-to-1 correspondence between your tables, and these:

tblElement - one row for each ELEMENT known to the system.
ElementID (PK)
ElementName
- any other attributes of the element as a whole; eg.

toxicity.

tblLot - one row for each LOT known to the system.
LotID (PK)
Processed - a yes/no flag to indicate whether this

LOT has been
processed into an END PRODUCT yet.
- any other attributes of the lot as a whole; eg. date

created.

tblLotElement - one row for each ELEMENT in each LOT.
LotID ( composite )
ElementID ( primary key )
- a field to give the amount or proportion of that

ELEMENT in that LOT.

I haven't included TESTS, because I still do not

understand your description
of those.

Aaron, I hope this helps. Note that I designed the table

structures above,
in about 1 minute (literally), once I understood your

data requirements.
That first "understanding" stage, is where most of the

time lies. Do not
try to design the tables during that stage. The trick is

to get an accurate
understanding of the data requirements, before you start

designing the
tables.

HTH,
TC


"Aaron" wrote in

message
...

-----Original Message (edited w/reply*)-----
Hi AAron

Sorry, but I still don't clearly understand the data

that
you're dealing
with.

Let's get the data descriptions correct, before we

proceed. This may seem
picky, but it is actually the most important step

in "data modelling". A
clear understanding of the data entities must come

first -
then the actual
design of the tables.

Is this correct:

- The research facility handles ELEMENTS, END PRODUCTS,

and PARTS.

- I assume that each ELEMENT has a unique ELEMENTID? If

not, how do you
distinguish different ELEMENTs? *Yes I have a table of

Elements: Autonumber ID and ElementName

- Two or more ELEMENTS are mixed together. The result

is
called a LOT. Each
LOT has a unique LOTID. There is no case where two LOTs

have the same LOTID. *Correct

- Each LOT is PROCESSED to form an END-PRODUCT.

- Is there only one LOT per END PRODUCT, or could you

process several LOTs
into a sinbgle END PRODUCT? *One lot per END PRODUCT

- I assume that each END PRODUCT has an ENDPRODUCTID?

If
not, how do you
distinguish different END PRODUCTs? *The LOTID assigned

at element composition is the same as the ID for the END
PRODUCT. The reason for the different table is because
since it can have more than 1 element and I need to
capture the amount/ratio of each Element used, I thought
it best to have the separate table. If you have another
suggestion please let me know.

- Each END PRODUCT is subject to one or more TESTS.

- I assume that each TEST has a unique TESTID. If not,

how do you
distinguish different TESTSs? *"Tests" are unique

fields
such as ParticleSize, ECvalue, FinalMass. Again, it's

more
of a characterization of the mixture of elements.

- Could one TEST handle several END PRODUCTs

simultaneously? *The Products are tested/characterized
independently but they all receive the same "tests".

Aaron, you may feel I am going backwards with my advice

to you! (We do not
seem to be getting down into the nitty gritty of that

table structures, at
all.) But the reason for this, is that you have not yet

clearly described
the data that you need to manage. So IMO, it is

premature
to comment on the
table structures.

Unfortunately I'll be busy for the next 2 days, & have

little or no time on
the net. But I'm happy to get back to this as soon as I

return. Or maybe
someone else will jump in & help.
Cheers,
TC

*I appreciate the assistance thus far and I understand

you
need to know the data.
--------------------------------------------------------

A research facility mixes elements(LotID),

processes
them,
evaluates the end product and then uses it in a

part
which
will also get tested.

So:
- each part has one (or several?) tests? several

types
of
tests
- each part comprises several end products? The

combined
elements(LotID) can be used in several Part#'s. Each

Part#
uses up to 2 LotID's(Front and Back) but the Front

LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?



"Aaron" wrote in

message
...

-----Original Message-----

"Aaron" wrote

in
message
...

(snipped)

A research facility mixes elements(LotID),

processes
them,
evaluates the end product and then uses it in a

part
which
will also get tested.

So:
- each part has one (or several?) tests? several

types
of
tests
- each part comprises several end products? The

combined
elements(LotID) can be used in several Part#'s. Each

Part#
uses up to 2 LotID's(Front and Back) but the Front

LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?


tblComposition (Lot# can be made up of 1-?

elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)

What is the primary key of that table? Actually I

have
a
autonumber CompID PK as well as a multi-field index
between LotID and Element.
What does one row in that table, represent?
Row1: Auto#,LotID112233,Al,60
Row2: Auto#,LotID112233,Cu,40
The concatenated result would be

LotID112233,AlCu,60:40



tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source
SourceID
What does one row in that table, represent? The

combined
elements from above are mixed with a source element

to
produce the final combination which will be used in a

part.


tblEvaluation (characterization of LotID's

after
mfg)
LotID (PK)one-one relationship to

tblMAnufacturing
ParticleSize
ECValue
FinalMAss

What is the primary key of that table? LotID is

actually
the PK
What does one row in that table,represent? (I don't
understand: "characterization of LotID's after
manufacturing". You need to describe the table in

terms
of
the entitites you
have mentioned so far: parts, tests, elements, end
products, etc.) The combined/processed elements are

tested
(characterized) before being used in Parts. Each row

will
contain the results for a LotID


tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)

Aaron, that is the only table that is clearly

described
so far. Each row in
that table represents a single, unique Part. Each

part
is
identified by a
unique Part#. Part# is the primary key of that

table.
We
need the same level
of understanding of your other tables, before we can
comment.

HTH,
TC



One of the key deliverables from this database

is
to
provide Evaluation data and Part listings for

every
combination of Elements and %Source

information.
i.e;
LOT#AALL12 had a 10%Source and was made up of
Al,Si,Cu at
a ratio of 60,20,20 and was used in
PART#1234,PART#5678,etc
If I continue down this path I will need to
concatenate
the records in the tblComposition and I don't

know
how big
a problem that will be. I also don't know what

other
problems I am not considering. Please advise.

Thanks.


.



.



.

  #10  
Old May 4th, 2004, 05:34 AM
TC
external usenet poster
 
Posts: n/a
Default Will my table design cause future problems

That sounds good. If the TEST fields are an attribute or characteristic of
the LOT as a whole, then they would go in tblLot. If it only made sense to
enter those fields after a lot had been processed into an end-product, the
relevant form(s) could check the Processed flag (in tblLot) to enforce that
restriction. For example, the Form_Current event could enable or disable the
TEST field controls, depending on whether the current tblLot record had or
had not been processed yet, respectively.

Good luck!
TC


"Aaron" wrote in message
...
Thanks again. So far your tables line up with what I was
thinking of doing. I'm thinking now that the TEST fields
could just be in tblLot instead of a separate table
because they are characteristics of LotID such as
ElectricalPerformance, AvgParticleSize, etc. ????

-----Original Message-----
Ok. Now we have the following clear description of your

needs:

"The research facility handles ELEMENTS, LOTS, END

PRODUCTS and TESTS. Two
or more ELEMENTS are mixed together, in various amounts

or proportions, into
a LOT. Each LOT is PROCESSED to form an END-PRODUCT.

(There is just one LOT
per END PRODUCT.) The END PRODUCT is then subject to

various TESTS."

Here is the correct table structure to meet those needs

(excluding TESTS).
Obviously your own table & field names may vary from

mine - but there should
be a 1-to-1 correspondence between your tables, and these:

tblElement - one row for each ELEMENT known to the system.
ElementID (PK)
ElementName
- any other attributes of the element as a whole; eg.

toxicity.

tblLot - one row for each LOT known to the system.
LotID (PK)
Processed - a yes/no flag to indicate whether this

LOT has been
processed into an END PRODUCT yet.
- any other attributes of the lot as a whole; eg. date

created.

tblLotElement - one row for each ELEMENT in each LOT.
LotID ( composite )
ElementID ( primary key )
- a field to give the amount or proportion of that

ELEMENT in that LOT.

I haven't included TESTS, because I still do not

understand your description
of those.

Aaron, I hope this helps. Note that I designed the table

structures above,
in about 1 minute (literally), once I understood your

data requirements.
That first "understanding" stage, is where most of the

time lies. Do not
try to design the tables during that stage. The trick is

to get an accurate
understanding of the data requirements, before you start

designing the
tables.

HTH,
TC


"Aaron" wrote in

message
...

-----Original Message (edited w/reply*)-----
Hi AAron

Sorry, but I still don't clearly understand the data

that
you're dealing
with.

Let's get the data descriptions correct, before we
proceed. This may seem
picky, but it is actually the most important step
in "data modelling". A
clear understanding of the data entities must come

first -
then the actual
design of the tables.

Is this correct:

- The research facility handles ELEMENTS, END PRODUCTS,
and PARTS.

- I assume that each ELEMENT has a unique ELEMENTID? If
not, how do you
distinguish different ELEMENTs? *Yes I have a table of
Elements: Autonumber ID and ElementName

- Two or more ELEMENTS are mixed together. The result

is
called a LOT. Each
LOT has a unique LOTID. There is no case where two LOTs
have the same LOTID. *Correct

- Each LOT is PROCESSED to form an END-PRODUCT.

- Is there only one LOT per END PRODUCT, or could you
process several LOTs
into a sinbgle END PRODUCT? *One lot per END PRODUCT

- I assume that each END PRODUCT has an ENDPRODUCTID?

If
not, how do you
distinguish different END PRODUCTs? *The LOTID assigned
at element composition is the same as the ID for the END
PRODUCT. The reason for the different table is because
since it can have more than 1 element and I need to
capture the amount/ratio of each Element used, I thought
it best to have the separate table. If you have another
suggestion please let me know.

- Each END PRODUCT is subject to one or more TESTS.

- I assume that each TEST has a unique TESTID. If not,
how do you
distinguish different TESTSs? *"Tests" are unique

fields
such as ParticleSize, ECvalue, FinalMass. Again, it's

more
of a characterization of the mixture of elements.

- Could one TEST handle several END PRODUCTs
simultaneously? *The Products are tested/characterized
independently but they all receive the same "tests".

Aaron, you may feel I am going backwards with my advice
to you! (We do not
seem to be getting down into the nitty gritty of that
table structures, at
all.) But the reason for this, is that you have not yet
clearly described
the data that you need to manage. So IMO, it is

premature
to comment on the
table structures.

Unfortunately I'll be busy for the next 2 days, & have
little or no time on
the net. But I'm happy to get back to this as soon as I
return. Or maybe
someone else will jump in & help.
Cheers,
TC

*I appreciate the assistance thus far and I understand

you
need to know the data.
--------------------------------------------------------

A research facility mixes elements(LotID),
processes
them,
evaluates the end product and then uses it in a
part
which
will also get tested.

So:
- each part has one (or several?) tests? several

types
of
tests
- each part comprises several end products? The
combined
elements(LotID) can be used in several Part#'s. Each
Part#
uses up to 2 LotID's(Front and Back) but the Front

LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?



"Aaron" wrote in
message
...

-----Original Message-----

"Aaron" wrote

in
message
...

(snipped)

A research facility mixes elements(LotID),
processes
them,
evaluates the end product and then uses it in a
part
which
will also get tested.

So:
- each part has one (or several?) tests? several

types
of
tests
- each part comprises several end products? The
combined
elements(LotID) can be used in several Part#'s. Each
Part#
uses up to 2 LotID's(Front and Back) but the Front

LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?


tblComposition (Lot# can be made up of 1-?
elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)

What is the primary key of that table? Actually I

have
a
autonumber CompID PK as well as a multi-field index
between LotID and Element.
What does one row in that table, represent?
Row1: Auto#,LotID112233,Al,60
Row2: Auto#,LotID112233,Cu,40
The concatenated result would be

LotID112233,AlCu,60:40



tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source
SourceID
What does one row in that table, represent? The
combined
elements from above are mixed with a source element

to
produce the final combination which will be used in a
part.


tblEvaluation (characterization of LotID's

after
mfg)
LotID (PK)one-one relationship to

tblMAnufacturing
ParticleSize
ECValue
FinalMAss

What is the primary key of that table? LotID is
actually
the PK
What does one row in that table,represent? (I don't
understand: "characterization of LotID's after
manufacturing". You need to describe the table in

terms
of
the entitites you
have mentioned so far: parts, tests, elements, end
products, etc.) The combined/processed elements are
tested
(characterized) before being used in Parts. Each row
will
contain the results for a LotID


tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)

Aaron, that is the only table that is clearly

described
so far. Each row in
that table represents a single, unique Part. Each

part
is
identified by a
unique Part#. Part# is the primary key of that

table.
We
need the same level
of understanding of your other tables, before we can
comment.

HTH,
TC



One of the key deliverables from this database

is
to
provide Evaluation data and Part listings for
every
combination of Elements and %Source

information.
i.e;
LOT#AALL12 had a 10%Source and was made up of
Al,Si,Cu at
a ratio of 60,20,20 and was used in
PART#1234,PART#5678,etc
If I continue down this path I will need to
concatenate
the records in the tblComposition and I don't

know
how big
a problem that will be. I also don't know what
other
problems I am not considering. Please advise.
Thanks.


.



.



.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 OfficeFrustration.
The comments are property of their posters.