|
| 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 |
|
#11
July 6th, 2007, 05:52 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Database relationships design
You have to remember, we're not there, if you don't give us
information, we can't make logical jumps and figure out what you're talking about. This: prod5_name field side_1_dimension side_2_dimension thickness longitudinal_weld is very different from this: Prod5Name Prod5_description1 Prod5_description2 Prod5_description3 Prod5_description4 Prod5_description5 Now, I understand your situation. I don't know what the best way to handle this is off the top of my head. This might be a situation where the output drives the design. What are some of the business requirements for this application? What sort of outputs are you expecting? Cheers, Jason Lepack On Jul 6, 12:30 pm, Catalin wrote: To make the story short I will give you some example. One of the products is - European hot rolled sections - "Europrofile" = prod_name field this is described as "type" = prod2_description as I caled it. Another product is - rectangular steel pipes = "rectangular_pipes" = prod5_name field described with - "side_1_dimension" = prod5_description1 - "side_2_ dimension" = prod5_description2 - "thickness" = prod5_description3 - "longitudinal_weld" = prod5_description4 So if you want to combine the products above I will have: ProdID - PK Prod_name type -used only in 90 rows for europrofiles side_1_dimension -used in 34 rows for rectangular pipes side_2_ dimension - same thickness - same longitudinal_weld -same Category -Fk and the total number of rows will be the sum for every product I have In conclussion what I want to say is that the fields for product tables are unique for each product. Thanks Catalin "Jason Lepack" wrote: I need an example of the data that you're storing, because you're not making any sense. On Jul 6, 11:38 am, Catalin wrote: Dear Jason, The description is not the same, so following your model I should have: Products: Prod_ID PK Prod_Name Prod1_description1 Prod1_description2 Prod2_description Prod3_description1 Prod3_description2 Prod3_description3 Prod3_description4 Prod4_description1 Prod4_description2 Prod4_description3 Prod5_description1 Prod5_description2 Prod5_description3 Prod5_description4 Prod5_description5 Prod6_description1 Prod6_description2 Prod6_description3 Prod7_description1 Prod7_description2 Categ - FK So you see that results a very nasty table which will have a lot of columns empty... Catalin "Jason Lepack" wrote: I can store all of that information in one table. Products: Prod_ID PK Prod_Name Prod_description1 Prod_description2 Prod_description3 Prod_description4 Prod_description5 Categ - FK Data: category_id, category_name 1, 'Tools' 2, 'Cars' 3, 'Hats' prod_id, prod_name, desc_1, desc_2, desc_3, desc_4, desc_5, categ 1, 'Pink Sombrero', 'wide', 'pink', 'round', 'short', 'doesnt fit in carry-on', 3 2, 'Adjustable Wrench', 'metal', 'fits different sized bolts', , , , 1 3, 'Porsche 911', 'fast', 'red', 'has 4 wheels', , , 2 Cheers, Jason Lepack On Jul 6, 9:38 am, Catalin wrote: I will give you the exact structure for the product tables: Prod_1 Prod1ID PK Prod1Name Prod1_description1 Prod1_description2 Categ - FK Prod_2 Prod2ID - PK Prod2Name Prod2_description Categ - FK Prod_3 Prod3ID PK Prod3Name Prod3_description1 Prod3_description2 Prod3_description3 Prod3_description4 Categ - FK Prod_4 Prod4ID PK Prod4Name Prod4_description1 Prod4_description2 Prod4_description3 Categ - FK Prod_5 Prod5ID PK Prod5Name Prod5_description1 Prod5_description2 Prod5_description3 Prod5_description4 Prod5_description5 Categ - FK Prod_6 Prod6ID PK Prod6Name Prod6_description1 Prod6_description2 Prod6_description3 Categ - FK Prod_7 Prod7ID PK Prod7Name Prod7_description1 Prod7_description2 Categ - FK The descriptions are different for one product to another. You do not have to worry because there will not be product 8 in the future. Thanks Catalin "Jason Lepack" wrote: Can you show me the structure of the other 5 tables? The examples that you have given don't illustrate a big difference in attributes. The number of products for each category does not matter. The problem that I have is that if you add another "type" (ie. prod_8) of item then you're going to have to renovate the whole application... That doesn't sound like fun to me. Cheers, Jason Lepack On Jul 6, 8:52 am, Catalin wrote: Dear Jason, The problem is that the products are totally different and cannot be described using similar attributes. Also the number of the products for each category differs a lot. Ex: prod_1 are 90 items, prod_2 are 55 a.s.o. Thanks, Catalin "Jason Lepack" wrote: Catalin, Why can't you put all the products in the same table? Your database will be a real pain to develop and maintain. I see that the only difference between prod_1 and prod_2 is a second description line. You could put all that data for prod_1 and prod_2 into prod_1 and leave the second description line null. Also, I suggest that for future postings you post your data with the information this way: Table_A: a_id - autonumber - pk a_name - text Table_B: b_id - pk a_id - int - fk - references Table_A.a_id b_name - text This way we will be able to make sense of your structure. Cheers, Jason Lepack On Jul 6, 8:14 am, Catalin wrote: Hi everyone, I am developing an Access database and I need some help to figure out how to link the table in the proper way. I have the tables which look like these Orders Order_detail Product_categ Prod_1 OrderID - PK DetailID - PK ProductCategory PK Prod1ID - PK ClientID - FK ProductCategory - FK Category Prod1Name Delivery_date OrderID - FK Prod1_description1 .... Quantity Prod1_description2 ..... Categ - FK Prod_2 Prod2ID - PK Prod2Name Prod2_description Categ - FK And so I have 7 products with different number and type of descriptions (which cannot be compacted in one table for all the products). This result in having one foreign key - Categ (which is PK in Product category table) - for 7 different other tables (product 1 to 7). I am not sure that this is the correct approach of this problem. Can anyone give me some better solution? Thanks very much in advance, Catalin- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - 
|
|
#12
July 6th, 2007, 06:50 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database relationships design
Sorry if I was so confusing.
Indeed it is an awkward situation here. Well, as I said in the beginning, I have a Clients table, Orders table, Orderdetail table which I have to link with the products tables (via product category due to the differences between the products) and to keep track of the quantities spent on each product and on each customer. it sounds quite simple but as u may see it is not. Catalin "Jason Lepack" wrote: You have to remember, we're not there, if you don't give us information, we can't make logical jumps and figure out what you're talking about. This: prod5_name field side_1_dimension side_2_dimension thickness longitudinal_weld is very different from this: Prod5Name Prod5_description1 Prod5_description2 Prod5_description3 Prod5_description4 Prod5_description5 Now, I understand your situation. I don't know what the best way to handle this is off the top of my head. This might be a situation where the output drives the design. What are some of the business requirements for this application? What sort of outputs are you expecting? Cheers, Jason Lepack On Jul 6, 12:30 pm, Catalin wrote: To make the story short I will give you some example. One of the products is - European hot rolled sections - "Europrofile" = prod_name field this is described as "type" = prod2_description as I caled it. Another product is - rectangular steel pipes = "rectangular_pipes" = prod5_name field described with - "side_1_dimension" = prod5_description1 - "side_2_ dimension" = prod5_description2 - "thickness" = prod5_description3 - "longitudinal_weld" = prod5_description4 So if you want to combine the products above I will have: ProdID - PK Prod_name type -used only in 90 rows for europrofiles side_1_dimension -used in 34 rows for rectangular pipes side_2_ dimension - same thickness - same longitudinal_weld -same Category -Fk and the total number of rows will be the sum for every product I have In conclussion what I want to say is that the fields for product tables are unique for each product. Thanks Catalin "Jason Lepack" wrote: I need an example of the data that you're storing, because you're not making any sense. On Jul 6, 11:38 am, Catalin wrote: Dear Jason, The description is not the same, so following your model I should have: Products: Prod_ID PK Prod_Name Prod1_description1 Prod1_description2 Prod2_description Prod3_description1 Prod3_description2 Prod3_description3 Prod3_description4 Prod4_description1 Prod4_description2 Prod4_description3 Prod5_description1 Prod5_description2 Prod5_description3 Prod5_description4 Prod5_description5 Prod6_description1 Prod6_description2 Prod6_description3 Prod7_description1 Prod7_description2 Categ - FK So you see that results a very nasty table which will have a lot of columns empty... Catalin "Jason Lepack" wrote: I can store all of that information in one table. Products: Prod_ID PK Prod_Name Prod_description1 Prod_description2 Prod_description3 Prod_description4 Prod_description5 Categ - FK Data: category_id, category_name 1, 'Tools' 2, 'Cars' 3, 'Hats' prod_id, prod_name, desc_1, desc_2, desc_3, desc_4, desc_5, categ 1, 'Pink Sombrero', 'wide', 'pink', 'round', 'short', 'doesnt fit in carry-on', 3 2, 'Adjustable Wrench', 'metal', 'fits different sized bolts', , , , 1 3, 'Porsche 911', 'fast', 'red', 'has 4 wheels', , , 2 Cheers, Jason Lepack On Jul 6, 9:38 am, Catalin wrote: I will give you the exact structure for the product tables: Prod_1 Prod1ID PK Prod1Name Prod1_description1 Prod1_description2 Categ - FK Prod_2 Prod2ID - PK Prod2Name Prod2_description Categ - FK Prod_3 Prod3ID PK Prod3Name Prod3_description1 Prod3_description2 Prod3_description3 Prod3_description4 Categ - FK Prod_4 Prod4ID PK Prod4Name Prod4_description1 Prod4_description2 Prod4_description3 Categ - FK Prod_5 Prod5ID PK Prod5Name Prod5_description1 Prod5_description2 Prod5_description3 Prod5_description4 Prod5_description5 Categ - FK Prod_6 Prod6ID PK Prod6Name Prod6_description1 Prod6_description2 Prod6_description3 Categ - FK Prod_7 Prod7ID PK Prod7Name Prod7_description1 Prod7_description2 Categ - FK The descriptions are different for one product to another. You do not have to worry because there will not be product 8 in the future. Thanks Catalin "Jason Lepack" wrote: Can you show me the structure of the other 5 tables? The examples that you have given don't illustrate a big difference in attributes. The number of products for each category does not matter. The problem that I have is that if you add another "type" (ie. prod_8) of item then you're going to have to renovate the whole application... That doesn't sound like fun to me. Cheers, Jason Lepack On Jul 6, 8:52 am, Catalin wrote: Dear Jason, The problem is that the products are totally different and cannot be described using similar attributes. Also the number of the products for each category differs a lot. Ex: prod_1 are 90 items, prod_2 are 55 a.s.o. Thanks, Catalin "Jason Lepack" wrote: Catalin, Why can't you put all the products in the same table? Your database will be a real pain to develop and maintain. I see that the only difference between prod_1 and prod_2 is a second description line. You could put all that data for prod_1 and prod_2 into prod_1 and leave the second description line null. Also, I suggest that for future postings you post your data with the information this way: Table_A: a_id - autonumber - pk a_name - text Table_B: b_id - pk a_id - int - fk - references Table_A.a_id b_name - text This way we will be able to make sense of your structure. Cheers, Jason Lepack On Jul 6, 8:14 am, Catalin wrote: Hi everyone, I am developing an Access database and I need some help to figure out how to link the table in the proper way. I have the tables which look like these Orders Order_detail Product_categ Prod_1 OrderID - PK DetailID - PK ProductCategory PK Prod1ID - PK ClientID - FK ProductCategory - FK Category Prod1Name Delivery_date OrderID - FK Prod1_description1 .... Quantity Prod1_description2 ..... Categ - FK Prod_2 Prod2ID - PK Prod2Name Prod2_description Categ - FK And so I have 7 products with different number and type of descriptions (which cannot be compacted in one table for all the products). This result in having one foreign key - Categ (which is PK in Product category table) - for 7 different other tables (product 1 to 7). I am not sure that this is the correct approach of this problem. Can anyone give me some better solution? Thanks very much in advance, Catalin- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -
|
|
#13
July 6th, 2007, 09:16 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database relationships design
IMO you are WAY over-thinking this design. The solution has already
(partially at least) been pointed out to you. I believe your issue is that you are confusing the database layer with the presentation layer. That's a common mistake if the new designer has mostly used speadsheet programs in the past. You'll need to break those two layers apart in your mind before you can make real progress. I'm not trying to smack you down with this post; I've been in the database design field for over 35 years. Kind regards, Dennis "Catalin" wrote: Sorry if I was so confusing. Indeed it is an awkward situation here. Well, as I said in the beginning, I have a Clients table, Orders table, Orderdetail table which I have to link with the products tables (via product category due to the differences between the products) and to keep track of the quantities spent on each product and on each customer. it sounds quite simple but as u may see it is not. Catalin "Jason Lepack" wrote: You have to remember, we're not there, if you don't give us information, we can't make logical jumps and figure out what you're talking about. This: prod5_name field side_1_dimension side_2_dimension thickness longitudinal_weld is very different from this: Prod5Name Prod5_description1 Prod5_description2 Prod5_description3 Prod5_description4 Prod5_description5 Now, I understand your situation. I don't know what the best way to handle this is off the top of my head. This might be a situation where the output drives the design. What are some of the business requirements for this application? What sort of outputs are you expecting? Cheers, Jason Lepack On Jul 6, 12:30 pm, Catalin wrote: To make the story short I will give you some example. One of the products is - European hot rolled sections - "Europrofile" = prod_name field this is described as "type" = prod2_description as I caled it. Another product is - rectangular steel pipes = "rectangular_pipes" = prod5_name field described with - "side_1_dimension" = prod5_description1 - "side_2_ dimension" = prod5_description2 - "thickness" = prod5_description3 - "longitudinal_weld" = prod5_description4 So if you want to combine the products above I will have: ProdID - PK Prod_name type -used only in 90 rows for europrofiles side_1_dimension -used in 34 rows for rectangular pipes side_2_ dimension - same thickness - same longitudinal_weld -same Category -Fk and the total number of rows will be the sum for every product I have In conclussion what I want to say is that the fields for product tables are unique for each product. Thanks Catalin "Jason Lepack" wrote: I need an example of the data that you're storing, because you're not making any sense. On Jul 6, 11:38 am, Catalin wrote: Dear Jason, The description is not the same, so following your model I should have: Products: Prod_ID PK Prod_Name Prod1_description1 Prod1_description2 Prod2_description Prod3_description1 Prod3_description2 Prod3_description3 Prod3_description4 Prod4_description1 Prod4_description2 Prod4_description3 Prod5_description1 Prod5_description2 Prod5_description3 Prod5_description4 Prod5_description5 Prod6_description1 Prod6_description2 Prod6_description3 Prod7_description1 Prod7_description2 Categ - FK So you see that results a very nasty table which will have a lot of columns empty... Catalin "Jason Lepack" wrote: I can store all of that information in one table. Products: Prod_ID PK Prod_Name Prod_description1 Prod_description2 Prod_description3 Prod_description4 Prod_description5 Categ - FK Data: category_id, category_name 1, 'Tools' 2, 'Cars' 3, 'Hats' prod_id, prod_name, desc_1, desc_2, desc_3, desc_4, desc_5, categ 1, 'Pink Sombrero', 'wide', 'pink', 'round', 'short', 'doesnt fit in carry-on', 3 2, 'Adjustable Wrench', 'metal', 'fits different sized bolts', , , , 1 3, 'Porsche 911', 'fast', 'red', 'has 4 wheels', , , 2 Cheers, Jason Lepack On Jul 6, 9:38 am, Catalin wrote: I will give you the exact structure for the product tables: Prod_1 Prod1ID PK Prod1Name Prod1_description1 Prod1_description2 Categ - FK Prod_2 Prod2ID - PK Prod2Name Prod2_description Categ - FK Prod_3 Prod3ID PK Prod3Name Prod3_description1 Prod3_description2 Prod3_description3 Prod3_description4 Categ - FK Prod_4 Prod4ID PK Prod4Name Prod4_description1 Prod4_description2 Prod4_description3 Categ - FK Prod_5 Prod5ID PK Prod5Name Prod5_description1 Prod5_description2 Prod5_description3 Prod5_description4 Prod5_description5 Categ - FK Prod_6 Prod6ID PK Prod6Name Prod6_description1 Prod6_description2 Prod6_description3 Categ - FK Prod_7 Prod7ID PK Prod7Name Prod7_description1 Prod7_description2 Categ - FK The descriptions are different for one product to another. You do not have to worry because there will not be product 8 in the future. Thanks Catalin "Jason Lepack" wrote: Can you show me the structure of the other 5 tables? The examples that you have given don't illustrate a big difference in attributes. The number of products for each category does not matter. The problem that I have is that if you add another "type" (ie. prod_8) of item then you're going to have to renovate the whole application... That doesn't sound like fun to me. Cheers, Jason Lepack On Jul 6, 8:52 am, Catalin wrote: Dear Jason, The problem is that the products are totally different and cannot be described using similar attributes. Also the number of the products for each category differs a lot. Ex: prod_1 are 90 items, prod_2 are 55 a.s.o. Thanks, Catalin "Jason Lepack" wrote: Catalin, Why can't you put all the products in the same table? Your database will be a real pain to develop and maintain. I see that the only difference between prod_1 and prod_2 is a second description line. You could put all that data for prod_1 and prod_2 into prod_1 and leave the second description line null. Also, I suggest that for future postings you post your data with the information this way: Table_A: a_id - autonumber - pk a_name - text Table_B: b_id - pk a_id - int - fk - references Table_A.a_id b_name - text This way we will be able to make sense of your structure. Cheers, Jason Lepack On Jul 6, 8:14 am, Catalin wrote: Hi everyone, I am developing an Access database and I need some help to figure out how to link the table in the proper way. I have the tables which look like these Orders Order_detail Product_categ Prod_1 OrderID - PK DetailID - PK ProductCategory PK Prod1ID - PK ClientID - FK ProductCategory - FK Category Prod1Name Delivery_date OrderID - FK Prod1_description1 .... Quantity Prod1_description2 ..... Categ - FK Prod_2 Prod2ID - PK Prod2Name Prod2_description Categ - FK And so I have 7 products with different number and type of descriptions (which cannot be compacted in one table for all the products). This result in having one foreign key - Categ (which is PK in Product category table) - for 7 different other tables (product 1 to 7). I am not sure that this is the correct approach of this problem. Can anyone give me some better solution? Thanks very much in advance, Catalin- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text -
|
|
#14
July 6th, 2007, 11:59 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database relationships design
On Fri, 6 Jul 2007 09:30:05 -0700, Catalin
wrote: To make the story short I will give you some example. One of the products is - European hot rolled sections - "Europrofile" = prod_name field this is described as "type" = prod2_description as I caled it. Another product is - rectangular steel pipes = "rectangular_pipes" = prod5_name field described with - "side_1_dimension" = prod5_description1 - "side_2_ dimension" = prod5_description2 - "thickness" = prod5_description3 - "longitudinal_weld" = prod5_description4 This may be a good case for "Subclassing". You could have one Products table with the fields that are in common for all products; this table would be related one-to-one to a series of additional tables with the type-specific fields. John W. Vinson [MVP]
|
|
#15
July 7th, 2007, 07:26 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database relationships design
Thank you very much for your answer.
I have one question. What can I do if there are no common fields for these products. I think that my example says what I mean. Shall I keep only the name of the products in one table? Can u also give me some more details about "subclassing" please ? Thanks in advance, Catalin "John W. Vinson" wrote: On Fri, 6 Jul 2007 09:30:05 -0700, Catalin wrote: To make the story short I will give you some example. One of the products is - European hot rolled sections - "Europrofile" = prod_name field this is described as "type" = prod2_description as I caled it. Another product is - rectangular steel pipes = "rectangular_pipes" = prod5_name field described with - "side_1_dimension" = prod5_description1 - "side_2_ dimension" = prod5_description2 - "thickness" = prod5_description3 - "longitudinal_weld" = prod5_description4 This may be a good case for "Subclassing". You could have one Products table with the fields that are in common for all products; this table would be related one-to-one to a series of additional tables with the type-specific fields. John W. Vinson [MVP]
|
|
#16
July 7th, 2007, 02:39 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database relationships design
Would it work to have one tblProductDescription with these fields:
ProductID ProductName ProductCategoryIDfk Description1 Description2 ..... DescriptionN And tblProductCategory with: ProductCategoryID ProductCategoryName FieldName1 FieldName2 .... FieldNameN Design an input form that changes the captions for the textboxes depending on the product category. IFAICT, it would work well for simple, text-based descriptions. If you wanted to apply data-validity rules to the various fields, it might become unwieldly. Or... you could even normalize the Descriptions and their fieldnames by putting them into separate tables. On Fri, 6 Jul 2007 06:38:05 -0700, Catalin wrote: I will give you the exact structure for the product tables: Prod_1 Prod1ID PK Prod1Name Prod1_description1 Prod1_description2 Categ - FK Prod_2 Prod2ID - PK Prod2Name Prod2_description Categ - FK Prod_3 Prod3ID PK Prod3Name Prod3_description1 Prod3_description2 Prod3_description3 Prod3_description4 Categ - FK Prod_4 Prod4ID PK Prod4Name Prod4_description1 Prod4_description2 Prod4_description3 Categ - FK Prod_5 Prod5ID PK Prod5Name Prod5_description1 Prod5_description2 Prod5_description3 Prod5_description4 Prod5_description5 Categ - FK Prod_6 Prod6ID PK Prod6Name Prod6_description1 Prod6_description2 Prod6_description3 Categ - FK Prod_7 Prod7ID PK Prod7Name Prod7_description1 Prod7_description2 Categ - FK The descriptions are different for one product to another. You do not have to worry because there will not be product 8 in the future. Thanks Catalin "Jason Lepack" wrote: Can you show me the structure of the other 5 tables? The examples that you have given don't illustrate a big difference in attributes. The number of products for each category does not matter. The problem that I have is that if you add another "type" (ie. prod_8) of item then you're going to have to renovate the whole application... That doesn't sound like fun to me. Cheers, Jason Lepack On Jul 6, 8:52 am, Catalin wrote: Dear Jason, The problem is that the products are totally different and cannot be described using similar attributes. Also the number of the products for each category differs a lot. Ex: prod_1 are 90 items, prod_2 are 55 a.s.o. Thanks, Catalin "Jason Lepack" wrote: Catalin, Why can't you put all the products in the same table? Your database will be a real pain to develop and maintain. I see that the only difference between prod_1 and prod_2 is a second description line. You could put all that data for prod_1 and prod_2 into prod_1 and leave the second description line null. Also, I suggest that for future postings you post your data with the information this way: Table_A: a_id - autonumber - pk a_name - text Table_B: b_id - pk a_id - int - fk - references Table_A.a_id b_name - text This way we will be able to make sense of your structure. Cheers, Jason Lepack On Jul 6, 8:14 am, Catalin wrote: Hi everyone, I am developing an Access database and I need some help to figure out how to link the table in the proper way. I have the tables which look like these Orders Order_detail Product_categ Prod_1 OrderID - PK DetailID - PK ProductCategory PK Prod1ID - PK ClientID - FK ProductCategory - FK Category Prod1Name Delivery_date OrderID - FK Prod1_description1 .... Quantity Prod1_description2 ..... Categ - FK Prod_2 Prod2ID - PK Prod2Name Prod2_description Categ - FK And so I have 7 products with different number and type of descriptions (which cannot be compacted in one table for all the products). This result in having one foreign key - Categ (which is PK in Product category table) - for 7 different other tables (product 1 to 7). I am not sure that this is the correct approach of this problem. Can anyone give me some better solution? Thanks very much in advance, Catalin- Hide quoted text - - Show quoted text - -- remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security
|
|
#17
July 7th, 2007, 04:20 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database relationships design
Dear friend,
I did not undeerstand quite well what u want to say. Could u be more explicit? Thanks a lot. Catalin "jacksonmacd" wrote: Would it work to have one tblProductDescription with these fields: ProductID ProductName ProductCategoryIDfk Description1 Description2 ..... DescriptionN And tblProductCategory with: ProductCategoryID ProductCategoryName FieldName1 FieldName2 .... FieldNameN Design an input form that changes the captions for the textboxes depending on the product category. IFAICT, it would work well for simple, text-based descriptions. If you wanted to apply data-validity rules to the various fields, it might become unwieldly. Or... you could even normalize the Descriptions and their fieldnames by putting them into separate tables. On Fri, 6 Jul 2007 06:38:05 -0700, Catalin wrote: I will give you the exact structure for the product tables: Prod_1 Prod1ID PK Prod1Name Prod1_description1 Prod1_description2 Categ - FK Prod_2 Prod2ID - PK Prod2Name Prod2_description Categ - FK Prod_3 Prod3ID PK Prod3Name Prod3_description1 Prod3_description2 Prod3_description3 Prod3_description4 Categ - FK Prod_4 Prod4ID PK Prod4Name Prod4_description1 Prod4_description2 Prod4_description3 Categ - FK Prod_5 Prod5ID PK Prod5Name Prod5_description1 Prod5_description2 Prod5_description3 Prod5_description4 Prod5_description5 Categ - FK Prod_6 Prod6ID PK Prod6Name Prod6_description1 Prod6_description2 Prod6_description3 Categ - FK Prod_7 Prod7ID PK Prod7Name Prod7_description1 Prod7_description2 Categ - FK The descriptions are different for one product to another. You do not have to worry because there will not be product 8 in the future. Thanks Catalin "Jason Lepack" wrote: Can you show me the structure of the other 5 tables? The examples that you have given don't illustrate a big difference in attributes. The number of products for each category does not matter. The problem that I have is that if you add another "type" (ie. prod_8) of item then you're going to have to renovate the whole application... That doesn't sound like fun to me. Cheers, Jason Lepack On Jul 6, 8:52 am, Catalin wrote: Dear Jason, The problem is that the products are totally different and cannot be described using similar attributes. Also the number of the products for each category differs a lot. Ex: prod_1 are 90 items, prod_2 are 55 a.s.o. Thanks, Catalin "Jason Lepack" wrote: Catalin, Why can't you put all the products in the same table? Your database will be a real pain to develop and maintain. I see that the only difference between prod_1 and prod_2 is a second description line. You could put all that data for prod_1 and prod_2 into prod_1 and leave the second description line null. Also, I suggest that for future postings you post your data with the information this way: Table_A: a_id - autonumber - pk a_name - text Table_B: b_id - pk a_id - int - fk - references Table_A.a_id b_name - text This way we will be able to make sense of your structure. Cheers, Jason Lepack On Jul 6, 8:14 am, Catalin wrote: Hi everyone, I am developing an Access database and I need some help to figure out how to link the table in the proper way. I have the tables which look like these Orders Order_detail Product_categ Prod_1 OrderID - PK DetailID - PK ProductCategory PK Prod1ID - PK ClientID - FK ProductCategory - FK Category Prod1Name Delivery_date OrderID - FK Prod1_description1 .... Quantity Prod1_description2 ..... Categ - FK Prod_2 Prod2ID - PK Prod2Name Prod2_description Categ - FK And so I have 7 products with different number and type of descriptions (which cannot be compacted in one table for all the products). This result in having one foreign key - Categ (which is PK in Product category table) - for 7 different other tables (product 1 to 7). I am not sure that this is the correct approach of this problem. Can anyone give me some better solution? Thanks very much in advance, Catalin- Hide quoted text - - Show quoted text - -- remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security
|
|
#18
July 8th, 2007, 01:32 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database relationships design
On Fri, 6 Jul 2007 23:26:00 -0700, Catalin
wrote: Thank you very much for your answer. I have one question. What can I do if there are no common fields for these products. I think that my example says what I mean. Shall I keep only the name of the products in one table? Can u also give me some more details about "subclassing" please ? You would need SOME sort of primary key: you must have a "handle" to uniquely identify the product in the rest of your application. It might be a ProductNo, a manually assigned product identifier; it might be an Autonumber - but you will need a primary key. Subclassing works by using a "master-child" table relationship, but a one to one relationship rather than the more common one to many. The master table would have the unique ProductID, the product name, and any other information which applies to all products. The child table would have the ProductID as its primary key as well - a Long Integer field if the master table primary key is an Autonumber - and fields which apply to just that category of product. A common example is a computer hardware inventory. You might have tables like: Hardware HardwareID LocationID HardwareType e.g. Monitor, Printer, Router, Desktop, ... other information that would apply to any kind of hardware ManufacturerID who made it ModelNumber manufacturer's identifier Monitors HardwareID Width Height Technology e.g. CRT, LCD, plasma screen, ... Printers HardwareID Technology e.g. laser, inkjet, impact, ... PageWidth PageHeight Resolution John W. Vinson [MVP]
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
