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
|
|||
|
|||
Table Desing Question
Hi, I have a database design question. A friend and I were having lunch and
got into a database table discussion. Neither one of us is experienced in database design or database application development. I showed my friend the database application that I am developing for a bakery/cafe that I plan on opening. My friend suggested I change my table design. This led into a discussion about which design is more efficient, the one that I am using or my friend’s design. My application is similar to designing a table structure for a family tree. There is a hierarchy of family members (great grandparent, grandparent, parent, and children). There are only leaf nodes in the family tree for the children, not the parents or grandparents. Since I am opening a Bakery/Cafe, you can imagine my application is analogous to the family tree example; but instead of people, it uses recipes. The recipes are accessed through a tree similar to the family tree analogy. For example, a "Chewy Chocolate Chip" Cookie recipe might be referenced by defining a recipe path-like structure similar to: Bakery/Cookies/Classic/Chocolate. Using this idea, one might design four tables to access the cookie recipe (tblBakery, tblCookies, tblClassic, tblChocolate). The cookie recipe is in the “leaf” table (i.e., tblChewyChoclateChip) pointed to by the four tables. In my design I don't actual use these table names, but I included them for understanding. I am using a table structure similar to: "tblMenu", "tblMenuCategory", "tblRecipeType", "tblRecipeCategory" and for the actual recipes, I defined a table called "tblRecipeNames". Each table has primary/foreign keys and the relationships between the tables are a one to many (for one entry in the “tblMenu” table, there can be several entries in the “tblMenuCategory” table (i.e., Bread points to Rye, Pan, Whole Wheat, Artisan, Quick bread, etc) My friend suggested that I use one table for all the recipe hierarchy information, similar to a link list, and one table for the recipes themselves. In my approach I defined five tables (four tables used for the recipe path and one table used for the recipes). In my friend’s approach there are only two tables in the design, one for the recipe path and one for the recipes. My design steps through four tables in order to access the recipes. In my friend’s design, the recipes are not limited to any nesting order because the recipe hierarchy table is structured like a link-list. My friend’s approach sounds simple enough; however, I’m concerned about table normalization and the tables becoming too large and unmanageable. If you have any suggestions or comments on this issue I would appreciate your feedback. Regards, Eddie |
#2
|
|||
|
|||
Your Design would be the most normalized and as a result would scale better
than the flat file your friend proposed. However, you may seriously want to consider a compromise. Four tables for your definition seems a little much. As a rule of thumb one of the questions you want to ask when "normalizing data" is "will this new structure compel me to query 2 or more tables to answer most questions" if so then you may have gone a little too far. You should not have all 2 column tables either. You can still have a bread type field, or menu type field without creating a whole table for it I would guess. The reason you'd want to consolidate tables is because later it may become actually confusing to look up something simple because you have to query 3 tables to do it. "Eddie's Bakery and Cafe'" wrote: Hi, I have a database design question. A friend and I were having lunch and got into a database table discussion. Neither one of us is experienced in database design or database application development. I showed my friend the database application that I am developing for a bakery/cafe that I plan on opening. My friend suggested I change my table design. This led into a discussion about which design is more efficient, the one that I am using or my friend’s design. My application is similar to designing a table structure for a family tree. There is a hierarchy of family members (great grandparent, grandparent, parent, and children). There are only leaf nodes in the family tree for the children, not the parents or grandparents. Since I am opening a Bakery/Cafe, you can imagine my application is analogous to the family tree example; but instead of people, it uses recipes. The recipes are accessed through a tree similar to the family tree analogy. For example, a "Chewy Chocolate Chip" Cookie recipe might be referenced by defining a recipe path-like structure similar to: Bakery/Cookies/Classic/Chocolate. Using this idea, one might design four tables to access the cookie recipe (tblBakery, tblCookies, tblClassic, tblChocolate). The cookie recipe is in the “leaf” table (i.e., tblChewyChoclateChip) pointed to by the four tables. In my design I don't actual use these table names, but I included them for understanding. I am using a table structure similar to: "tblMenu", "tblMenuCategory", "tblRecipeType", "tblRecipeCategory" and for the actual recipes, I defined a table called "tblRecipeNames". Each table has primary/foreign keys and the relationships between the tables are a one to many (for one entry in the “tblMenu” table, there can be several entries in the “tblMenuCategory” table (i.e., Bread points to Rye, Pan, Whole Wheat, Artisan, Quick bread, etc) My friend suggested that I use one table for all the recipe hierarchy information, similar to a link list, and one table for the recipes themselves. In my approach I defined five tables (four tables used for the recipe path and one table used for the recipes). In my friend’s approach there are only two tables in the design, one for the recipe path and one for the recipes. My design steps through four tables in order to access the recipes. In my friend’s design, the recipes are not limited to any nesting order because the recipe hierarchy table is structured like a link-list. My friend’s approach sounds simple enough; however, I’m concerned about table normalization and the tables becoming too large and unmanageable. If you have any suggestions or comments on this issue I would appreciate your feedback. Regards, Eddie |
#3
|
|||
|
|||
"taylormade" wrote: Your Design would be the most normalized and as a result would scale better than the flat file your friend proposed. However, you may seriously want to consider a compromise. Four tables for your definition seems a little much. As a rule of thumb one of the questions you want to ask when "normalizing data" is "will this new structure compel me to query 2 or more tables to answer most questions" if so then you may have gone a little too far. You should not have all 2 column tables either. You can still have a bread type field, or menu type field without creating a whole table for it I would guess. The reason you'd want to consolidate tables is because later it may become actually confusing to look up something simple because you have to query 3 tables to do it. "Eddie's Bakery and Cafe'" wrote: Hi, I have a database design question. A friend and I were having lunch and got into a database table discussion. Neither one of us is experienced in database design or database application development. I showed my friend the database application that I am developing for a bakery/cafe that I plan on opening. My friend suggested I change my table design. This led into a discussion about which design is more efficient, the one that I am using or my friend’s design. My application is similar to designing a table structure for a family tree. There is a hierarchy of family members (great grandparent, grandparent, parent, and children). There are only leaf nodes in the family tree for the children, not the parents or grandparents. Since I am opening a Bakery/Cafe, you can imagine my application is analogous to the family tree example; but instead of people, it uses recipes. The recipes are accessed through a tree similar to the family tree analogy. For example, a "Chewy Chocolate Chip" Cookie recipe might be referenced by defining a recipe path-like structure similar to: Bakery/Cookies/Classic/Chocolate. Using this idea, one might design four tables to access the cookie recipe (tblBakery, tblCookies, tblClassic, tblChocolate). The cookie recipe is in the “leaf” table (i.e., tblChewyChoclateChip) pointed to by the four tables. In my design I don't actual use these table names, but I included them for understanding. I am using a table structure similar to: "tblMenu", "tblMenuCategory", "tblRecipeType", "tblRecipeCategory" and for the actual recipes, I defined a table called "tblRecipeNames". Each table has primary/foreign keys and the relationships between the tables are a one to many (for one entry in the “tblMenu” table, there can be several entries in the “tblMenuCategory” table (i.e., Bread points to Rye, Pan, Whole Wheat, Artisan, Quick bread, etc) My friend suggested that I use one table for all the recipe hierarchy information, similar to a link list, and one table for the recipes themselves. In my approach I defined five tables (four tables used for the recipe path and one table used for the recipes). In my friend’s approach there are only two tables in the design, one for the recipe path and one for the recipes. My design steps through four tables in order to access the recipes. In my friend’s design, the recipes are not limited to any nesting order because the recipe hierarchy table is structured like a link-list. My friend’s approach sounds simple enough; however, I’m concerned about table normalization and the tables becoming too large and unmanageable. If you have any suggestions or comments on this issue I would appreciate your feedback. Regards, Eddie |
#4
|
|||
|
|||
Thanks for the response. Your answer makes a lot of sense.
"taylormade" wrote: Your Design would be the most normalized and as a result would scale better than the flat file your friend proposed. However, you may seriously want to consider a compromise. Four tables for your definition seems a little much. As a rule of thumb one of the questions you want to ask when "normalizing data" is "will this new structure compel me to query 2 or more tables to answer most questions" if so then you may have gone a little too far. You should not have all 2 column tables either. You can still have a bread type field, or menu type field without creating a whole table for it I would guess. The reason you'd want to consolidate tables is because later it may become actually confusing to look up something simple because you have to query 3 tables to do it. "Eddie's Bakery and Cafe'" wrote: Hi, I have a database design question. A friend and I were having lunch and got into a database table discussion. Neither one of us is experienced in database design or database application development. I showed my friend the database application that I am developing for a bakery/cafe that I plan on opening. My friend suggested I change my table design. This led into a discussion about which design is more efficient, the one that I am using or my friend’s design. My application is similar to designing a table structure for a family tree. There is a hierarchy of family members (great grandparent, grandparent, parent, and children). There are only leaf nodes in the family tree for the children, not the parents or grandparents. Since I am opening a Bakery/Cafe, you can imagine my application is analogous to the family tree example; but instead of people, it uses recipes. The recipes are accessed through a tree similar to the family tree analogy. For example, a "Chewy Chocolate Chip" Cookie recipe might be referenced by defining a recipe path-like structure similar to: Bakery/Cookies/Classic/Chocolate. Using this idea, one might design four tables to access the cookie recipe (tblBakery, tblCookies, tblClassic, tblChocolate). The cookie recipe is in the “leaf” table (i.e., tblChewyChoclateChip) pointed to by the four tables. In my design I don't actual use these table names, but I included them for understanding. I am using a table structure similar to: "tblMenu", "tblMenuCategory", "tblRecipeType", "tblRecipeCategory" and for the actual recipes, I defined a table called "tblRecipeNames". Each table has primary/foreign keys and the relationships between the tables are a one to many (for one entry in the “tblMenu” table, there can be several entries in the “tblMenuCategory” table (i.e., Bread points to Rye, Pan, Whole Wheat, Artisan, Quick bread, etc) My friend suggested that I use one table for all the recipe hierarchy information, similar to a link list, and one table for the recipes themselves. In my approach I defined five tables (four tables used for the recipe path and one table used for the recipes). In my friend’s approach there are only two tables in the design, one for the recipe path and one for the recipes. My design steps through four tables in order to access the recipes. In my friend’s design, the recipes are not limited to any nesting order because the recipe hierarchy table is structured like a link-list. My friend’s approach sounds simple enough; however, I’m concerned about table normalization and the tables becoming too large and unmanageable. If you have any suggestions or comments on this issue I would appreciate your feedback. Regards, Eddie |
#5
|
|||
|
|||
In a family tree structure there is only ONE table for people. There is a
second table to define relationships. The relation table contains personID1, personID2, and RelationshipType such as birthMother, birthFather, Husband. Normally, you would only make the relationship to the immediate parent. The grandparent and greatgrandparents can be derived by walking up the tree and the children, grandchildren, etc can be derived by walking down the tree. The example you presented of your tables is not normalized. It would have been better if you described your actual tables. However, if you in fact have a table named tblCookie or tblChocolate, you are definitely heading in the wrong direction. The implication of those table names is that you have a table for each recipe type and major ingredient. Basically you need three tables - tblRecipe, tblIngredient, tblRecipeIngredient. tblRecipeIngredient is the relation table between recipes and their ingredients. If you ever want to do any cost accounting, you will need this information to figure out how much a recipe costs to make. It will also help you if you want to find recipes that use large amounts of a certain ingredient. Say you get a great deal on walnuts but you have to use them before they get stale. You could look up recipes that use 16 oz of walnuts and make those for your special this week. To expand on this basic structure, you would probably want category tables that let you group recipes into cookies, pies, cakes, etc. You may want a category hierarchy so you can break pies down in to fruit, cream, custard, no bake, etc. "Eddie's Bakery and Cafe'" wrote in message ... Hi, I have a database design question. A friend and I were having lunch and got into a database table discussion. Neither one of us is experienced in database design or database application development. I showed my friend the database application that I am developing for a bakery/cafe that I plan on opening. My friend suggested I change my table design. This led into a discussion about which design is more efficient, the one that I am using or my friend's design. My application is similar to designing a table structure for a family tree. There is a hierarchy of family members (great grandparent, grandparent, parent, and children). There are only leaf nodes in the family tree for the children, not the parents or grandparents. Since I am opening a Bakery/Cafe, you can imagine my application is analogous to the family tree example; but instead of people, it uses recipes. The recipes are accessed through a tree similar to the family tree analogy. For example, a "Chewy Chocolate Chip" Cookie recipe might be referenced by defining a recipe path-like structure similar to: Bakery/Cookies/Classic/Chocolate. Using this idea, one might design four tables to access the cookie recipe (tblBakery, tblCookies, tblClassic, tblChocolate). The cookie recipe is in the "leaf" table (i.e., tblChewyChoclateChip) pointed to by the four tables. In my design I don't actual use these table names, but I included them for understanding. I am using a table structure similar to: "tblMenu", "tblMenuCategory", "tblRecipeType", "tblRecipeCategory" and for the actual recipes, I defined a table called "tblRecipeNames". Each table has primary/foreign keys and the relationships between the tables are a one to many (for one entry in the "tblMenu" table, there can be several entries in the "tblMenuCategory" table (i.e., Bread points to Rye, Pan, Whole Wheat, Artisan, Quick bread, etc) My friend suggested that I use one table for all the recipe hierarchy information, similar to a link list, and one table for the recipes themselves. In my approach I defined five tables (four tables used for the recipe path and one table used for the recipes). In my friend's approach there are only two tables in the design, one for the recipe path and one for the recipes. My design steps through four tables in order to access the recipes. In my friend's design, the recipes are not limited to any nesting order because the recipe hierarchy table is structured like a link-list. My friend's approach sounds simple enough; however, I'm concerned about table normalization and the tables becoming too large and unmanageable. If you have any suggestions or comments on this issue I would appreciate your feedback. Regards, Eddie |
#6
|
|||
|
|||
Hi Pat, Thanks for taking the question. No I don't have a table for every
recipe. I did a poor job in describing the design. I only have one table for all the recipes and four other tables that describe that break everything down into menus (main and category) and a recipe path (type and subtype). Your feedback was very helpful. Thanks Eddie "Pat Hartman" wrote: In a family tree structure there is only ONE table for people. There is a second table to define relationships. The relation table contains personID1, personID2, and RelationshipType such as birthMother, birthFather, Husband. Normally, you would only make the relationship to the immediate parent. The grandparent and greatgrandparents can be derived by walking up the tree and the children, grandchildren, etc can be derived by walking down the tree. The example you presented of your tables is not normalized. It would have been better if you described your actual tables. However, if you in fact have a table named tblCookie or tblChocolate, you are definitely heading in the wrong direction. The implication of those table names is that you have a table for each recipe type and major ingredient. Basically you need three tables - tblRecipe, tblIngredient, tblRecipeIngredient. tblRecipeIngredient is the relation table between recipes and their ingredients. If you ever want to do any cost accounting, you will need this information to figure out how much a recipe costs to make. It will also help you if you want to find recipes that use large amounts of a certain ingredient. Say you get a great deal on walnuts but you have to use them before they get stale. You could look up recipes that use 16 oz of walnuts and make those for your special this week. To expand on this basic structure, you would probably want category tables that let you group recipes into cookies, pies, cakes, etc. You may want a category hierarchy so you can break pies down in to fruit, cream, custard, no bake, etc. "Eddie's Bakery and Cafe'" wrote in message ... Hi, I have a database design question. A friend and I were having lunch and got into a database table discussion. Neither one of us is experienced in database design or database application development. I showed my friend the database application that I am developing for a bakery/cafe that I plan on opening. My friend suggested I change my table design. This led into a discussion about which design is more efficient, the one that I am using or my friend's design. My application is similar to designing a table structure for a family tree. There is a hierarchy of family members (great grandparent, grandparent, parent, and children). There are only leaf nodes in the family tree for the children, not the parents or grandparents. Since I am opening a Bakery/Cafe, you can imagine my application is analogous to the family tree example; but instead of people, it uses recipes. The recipes are accessed through a tree similar to the family tree analogy. For example, a "Chewy Chocolate Chip" Cookie recipe might be referenced by defining a recipe path-like structure similar to: Bakery/Cookies/Classic/Chocolate. Using this idea, one might design four tables to access the cookie recipe (tblBakery, tblCookies, tblClassic, tblChocolate). The cookie recipe is in the "leaf" table (i.e., tblChewyChoclateChip) pointed to by the four tables. In my design I don't actual use these table names, but I included them for understanding. I am using a table structure similar to: "tblMenu", "tblMenuCategory", "tblRecipeType", "tblRecipeCategory" and for the actual recipes, I defined a table called "tblRecipeNames". Each table has primary/foreign keys and the relationships between the tables are a one to many (for one entry in the "tblMenu" table, there can be several entries in the "tblMenuCategory" table (i.e., Bread points to Rye, Pan, Whole Wheat, Artisan, Quick bread, etc) My friend suggested that I use one table for all the recipe hierarchy information, similar to a link list, and one table for the recipes themselves. In my approach I defined five tables (four tables used for the recipe path and one table used for the recipes). In my friend's approach there are only two tables in the design, one for the recipe path and one for the recipes. My design steps through four tables in order to access the recipes. In my friend's design, the recipes are not limited to any nesting order because the recipe hierarchy table is structured like a link-list. My friend's approach sounds simple enough; however, I'm concerned about table normalization and the tables becoming too large and unmanageable. If you have any suggestions or comments on this issue I would appreciate your feedback. Regards, Eddie |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Update Tables using forms | achett | Using Forms | 5 | January 28th, 2005 12:25 AM |
Mixed up with Relationships..help! | KrazyRed | New Users | 3 | January 26th, 2005 05:03 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |