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
|
|||
|
|||
Advice on table design strategy?
I am working my way through my first Access project in many years.
I could use some advice on how to proceed. The database is set up to track ordered items. We have a pre-defined set of all possible components we might order (this in a table called Components). There is another table of PurchaseOrders (basically just a list of unique PO numbers we have issued). These entries in these two tables were used to populate a very large table called OrderedItems. The OrderedItems table has a record for each individual Component ordered along with the PO it was ordered on. All these tables and supporting forms are now in place and reasonably functional. Now I need to collect OrderedItems into Assemblies. An assembly will be a unique identifiable physical collection of parts(OrderedItems) that will be assembled together and delivered to a customer. There will be a finite set of permutations of components defining a particular Assembly. I am considering adding another table called Configurations. A Configuration would be the "recipe" that dictates which Component types, and what quantity, will go together. For example, Configuration #1 might consist of (1) piece of Component (type) #15, (2) piece of Component #18, and (1) piece of Component #20. Configuration #2 might consist of (2) pieces of Component #7 and (1) piece of Component #12. A given Configuration will be the basis for many Assemblies. Note that I will not always have the same total number of parts (components) for the different Configurations. Here is where I am getting hazy. Should I structure my Configuration table with some arbitrary maximum number of defined components in mind? Example A: Here is how I might structure the Configuration table for a maximum of (4) unique components: ConfigIndex (primary key) CompAType CompAQty CompBType CompBQty CompCType CompCQty CompDType CompDQty I guess this might work if I am allowed to leave some of the fields blank in some cases (?) My other option might be to structure my Configuration Table as follows: Example B: ConfigIndex (primary key) ConfigType CompType CompQty -- where I define ConfigType (an integer) in a separate small table. In this case the records would have less fields, there would be no blank fields, but I would have more records. I might have 3 records in a row defining ConfigType #1, then the next 4 records defining ConfigType #2. Once I have the Configuration table set up, I would like to be able to use it to populate my Assembly table. Assembly #1 might use Configuration #17. I would like to use the Configuration table to control a form and query which would allow me to select only the required (and available) Components from my OrderedItems table. I have an "Assembly" field in my OrderedItems table which will link each physical item in OrderedItems to a particular Assembly index. Again, the Configuration table could be thought of as the total list of "recipes". The Assemblies table will describe physical unique assemblies that bring together collections of Ordered Items. I hope this was not too rambling and it makes some sense. I would appreciate any comments or ideas on how I should handle the "Configurations" (using Example A, Example B, or some other method). At this point Example A seems like the prudent way to go, to me... Thanks in advance for your suggestions! - Doug -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200803/1 |
#2
|
|||
|
|||
Advice on table design strategy?
Doug
If you try to set up a maximum number of components, the universe (or your boss) will find a way to exceed that number by one g! And since one Assembly might have two components, while another might have 5, you are better off dropping the spreadsheet (add another column!) approach and using a relational (1 assembly can have 1-many components) design. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "DougW via AccessMonster.com" u42278@uwe wrote in message news:81b304d9f0657@uwe... I am working my way through my first Access project in many years. I could use some advice on how to proceed. The database is set up to track ordered items. We have a pre-defined set of all possible components we might order (this in a table called Components). There is another table of PurchaseOrders (basically just a list of unique PO numbers we have issued). These entries in these two tables were used to populate a very large table called OrderedItems. The OrderedItems table has a record for each individual Component ordered along with the PO it was ordered on. All these tables and supporting forms are now in place and reasonably functional. Now I need to collect OrderedItems into Assemblies. An assembly will be a unique identifiable physical collection of parts(OrderedItems) that will be assembled together and delivered to a customer. There will be a finite set of permutations of components defining a particular Assembly. I am considering adding another table called Configurations. A Configuration would be the "recipe" that dictates which Component types, and what quantity, will go together. For example, Configuration #1 might consist of (1) piece of Component (type) #15, (2) piece of Component #18, and (1) piece of Component #20. Configuration #2 might consist of (2) pieces of Component #7 and (1) piece of Component #12. A given Configuration will be the basis for many Assemblies. Note that I will not always have the same total number of parts (components) for the different Configurations. Here is where I am getting hazy. Should I structure my Configuration table with some arbitrary maximum number of defined components in mind? Example A: Here is how I might structure the Configuration table for a maximum of (4) unique components: ConfigIndex (primary key) CompAType CompAQty CompBType CompBQty CompCType CompCQty CompDType CompDQty I guess this might work if I am allowed to leave some of the fields blank in some cases (?) My other option might be to structure my Configuration Table as follows: Example B: ConfigIndex (primary key) ConfigType CompType CompQty -- where I define ConfigType (an integer) in a separate small table. In this case the records would have less fields, there would be no blank fields, but I would have more records. I might have 3 records in a row defining ConfigType #1, then the next 4 records defining ConfigType #2. Once I have the Configuration table set up, I would like to be able to use it to populate my Assembly table. Assembly #1 might use Configuration #17. I would like to use the Configuration table to control a form and query which would allow me to select only the required (and available) Components from my OrderedItems table. I have an "Assembly" field in my OrderedItems table which will link each physical item in OrderedItems to a particular Assembly index. Again, the Configuration table could be thought of as the total list of "recipes". The Assemblies table will describe physical unique assemblies that bring together collections of Ordered Items. I hope this was not too rambling and it makes some sense. I would appreciate any comments or ideas on how I should handle the "Configurations" (using Example A, Example B, or some other method). At this point Example A seems like the prudent way to go, to me... Thanks in advance for your suggestions! - Doug -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200803/1 |
#3
|
|||
|
|||
Advice on table design strategy?
"DougW via AccessMonster.com" u42278@uwe wrote in message news:81b304d9f0657@uwe... I am working my way through my first Access project in many years. I could use some advice on how to proceed. The database is set up to track ordered items. We have a pre-defined set of all possible components we might order (this in a table called Components). There is another table of PurchaseOrders (basically just a list of unique PO numbers we have issued). These entries in these two tables were used to populate a very large table called OrderedItems. The OrderedItems table has a record for each individual Component ordered along with the PO it was ordered on. All these tables and supporting forms are now in place and reasonably functional. Now I need to collect OrderedItems into Assemblies. An assembly will be a unique identifiable physical collection of parts(OrderedItems) that will be assembled together and delivered to a customer. There will be a finite set of permutations of components defining a particular Assembly. I am considering adding another table called Configurations. A Configuration would be the "recipe" that dictates which Component types, and what quantity, will go together. For example, Configuration #1 might consist of (1) piece of Component (type) #15, (2) piece of Component #18, and (1) piece of Component #20. Configuration #2 might consist of (2) pieces of Component #7 and (1) piece of Component #12. A given Configuration will be the basis for many Assemblies. Note that I will not always have the same total number of parts (components) for the different Configurations. Here is where I am getting hazy. Should I structure my Configuration table with some arbitrary maximum number of defined components in mind? Example A: Here is how I might structure the Configuration table for a maximum of (4) unique components: ConfigIndex (primary key) CompAType CompAQty CompBType CompBQty CompCType CompCQty CompDType CompDQty I guess this might work if I am allowed to leave some of the fields blank in some cases (?) My other option might be to structure my Configuration Table as follows: Example B: ConfigIndex (primary key) ConfigType CompType CompQty -- where I define ConfigType (an integer) in a separate small table. In this case the records would have less fields, there would be no blank fields, but I would have more records. I might have 3 records in a row defining ConfigType #1, then the next 4 records defining ConfigType #2. Once I have the Configuration table set up, I would like to be able to use it to populate my Assembly table. Assembly #1 might use Configuration #17. I would like to use the Configuration table to control a form and query which would allow me to select only the required (and available) Components from my OrderedItems table. I have an "Assembly" field in my OrderedItems table which will link each physical item in OrderedItems to a particular Assembly index. Again, the Configuration table could be thought of as the total list of "recipes". The Assemblies table will describe physical unique assemblies that bring together collections of Ordered Items. I hope this was not too rambling and it makes some sense. I would appreciate any comments or ideas on how I should handle the "Configurations" (using Example A, Example B, or some other method). At this point Example A seems like the prudent way to go, to me... Thanks in advance for your suggestions! - Doug -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200803/1 Jeff is right, avoid repeating groups. What I am not following is the need for a Configuration table Why can you just create and AssemblyComponets table with all legal Assemblies and the quantity of acomponent for that Assemply. What am I missing? Also, what exactly appears on your Purchase Order? Evan |
#4
|
|||
|
|||
Advice on table design strategy?
Jeff, thank you for the advice. I will try to structure it as you
recommended (similar to my Example B). I also agree with your insight on the behavior of the universe and bosses Evan, I should clarify a bit, I probably left out some relevant details. The Purchase Orders are orders where my company orders machined components used in our equipment. We take the parts from various POs and assemble them into Assemblies used by our customers ("customers" within our company actually). The Purchase Order table is basically just a list of our POs we have issued and the vendor's name. It's relatively small. We can have many parts ordered on a single PO. Parts from one PO can end up on many different Assemblies. Those parts are line items on the POs - the individual parts show up in my OrderedItems table. That's the really big table in my DB. I think of that table as being the "middle" of my DB. The list of unique part types (think drawing number) are contained in my Components table. The list of all physical parts ordered (think serial number) are contained in my OrderedItems table. The records in OrderedItems point to the part type in the Components table and the PO in the PurchaseOrder table on which that particular item was ordered. The Configuration table would be similar to the Components table in that it would define "types" or "models" of all legal assemblies. The Assemblies table would be similar to the OrderedItems table in that it would contain records of actual physical assemblies produced. An analogy would be that the Configuration table would be a list of car models - the Assemblies table would be a list of VIN numbers of actual cars produced. My vision is to somehow arrange this so that I can populate my Configuration table with the definitions of all my legal assembly types, containing the Components (part types) used for that configuration. Then when working from a form linked to my Assemblies table, I would like to be able to select a configuration for a particular assembly, which would then generate a list of those available (unassigned) OrderedItems that are needed for that configuration. Kind of picking parts from baskets and putting them together in another basket - and I would only be presented with the "baskets" (lists) appropriate to my desired configuration (maybe not a great analogy). I referred to available or unassigned OrderedItems. I have a field in OrderedItems reserved for the Assembly number. So the result of my form transaction that I described in the previous paragraph, would be that a number of OrderedItems records would be updated with one particular Assembly number. The purpose of the whole DB is to allow us to correlate all the parts we order on the PO's, to the Assemblies that we eventually deliver to our internal customers. We should be able to run queries or reports that will use the promised ship dates of parts from our vendors on PO's, and calculate the resulting projected delivery date of a given Assembly (looking at the latest shipping date of all the parts contained in that Assembly), for one example. I hope that made some sense. Maybe there is some way to incorporate the Configuration function into the Assembly table as you suggested? I'm wide open to any suggestions. Thank you very much for your guidance, it is truly appreciated! -- Doug Evan Keel wrote: I am working my way through my first Access project in many years. I could use some advice on how to proceed. [quoted text clipped - 75 lines] Thanks in advance for your suggestions! - Doug http://www.accessmonster.com/Uwe/For...esign/200803/1 Jeff is right, avoid repeating groups. What I am not following is the need for a Configuration table Why can you just create and AssemblyComponets table with all legal Assemblies and the quantity of acomponent for that Assemply. What am I missing? Also, what exactly appears on your Purchase Order? Evan -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200803/1 |
Thread Tools | |
Display Modes | |
|
|