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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|