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
|
|||
|
|||
Groups
I’m having trouble on how to design the tables for my database. Currently I
have 10 locations and they report their revenue each month by classification. There are 10 different classifications. Now the classifications are also classified into 5 groups and the classifications can be in one or more groups. I would like to establish the groups and just have data fall into the rightful group the classification is in. Not sure how to accomplish this. Any ideas? |
#2
|
|||
|
|||
Groups
Does this help? Locations - Revenue - Groups - Classification " - " means "1-to-many" in that direction -- The 11th day of every month: http://truthaction.org/forum/index.php "Marc" wrote: I’m having trouble on how to design the tables for my database. Currently I have 10 locations and they report their revenue each month by classification. There are 10 different classifications. Now the classifications are also classified into 5 groups and the classifications can be in one or more groups. I would like to establish the groups and just have data fall into the rightful group the classification is in. Not sure how to accomplish this. Any ideas? |
#3
|
|||
|
|||
Groups
In fact it should be: Locations - Revenue - Classification - Groups tble_group GroupID (PK) tble_Class GroupID (FK) ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) All IDs should ideally be long integer numbers. -- The 11th day of every month: http://truthaction.org/forum/index.php "Marc" wrote: I’m having trouble on how to design the tables for my database. Currently I have 10 locations and they report their revenue each month by classification. There are 10 different classifications. Now the classifications are also classified into 5 groups and the classifications can be in one or more groups. I would like to establish the groups and just have data fall into the rightful group the classification is in. Not sure how to accomplish this. Any ideas? |
#4
|
|||
|
|||
Groups
Thank you for the response.
Say I have one classID and it falls into 3 different groups. How do I assign one classID to all 3 groups? "scubadiver" wrote: In fact it should be: Locations - Revenue - Classification - Groups tble_group GroupID (PK) tble_Class GroupID (FK) ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) All IDs should ideally be long integer numbers. -- The 11th day of every month: http://truthaction.org/forum/index.php "Marc" wrote: I’m having trouble on how to design the tables for my database. Currently I have 10 locations and they report their revenue each month by classification. There are 10 different classifications. Now the classifications are also classified into 5 groups and the classifications can be in one or more groups. I would like to establish the groups and just have data fall into the rightful group the classification is in. Not sure how to accomplish this. Any ideas? |
#5
|
|||
|
|||
Groups
That is what confused me slightly. So maybe it should be: Locations - Revenue - Classification - ClassGroup - Groups "Revenue" and "ClassGroup" are junction tables tble_group GroupID (PK) tble_classgroup GroupID (FK) ClassID (FK) tble_Class ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) Set up these tables and go to tools - references. Show all the tables and then drag the appropriate fields between the appropriate tables then you can visualise the relationships. For each relationship establish referential integrity. -- http://www.ready4mainstream.ny911truth.org/index.html "Marc" wrote: Thank you for the response. Say I have one classID and it falls into 3 different groups. How do I assign one classID to all 3 groups? "scubadiver" wrote: In fact it should be: Locations - Revenue - Classification - Groups tble_group GroupID (PK) tble_Class GroupID (FK) ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) All IDs should ideally be long integer numbers. -- The 11th day of every month: http://truthaction.org/forum/index.php "Marc" wrote: I’m having trouble on how to design the tables for my database. Currently I have 10 locations and they report their revenue each month by classification. There are 10 different classifications. Now the classifications are also classified into 5 groups and the classifications can be in one or more groups. I would like to establish the groups and just have data fall into the rightful group the classification is in. Not sure how to accomplish this. Any ideas? |
#6
|
|||
|
|||
Groups
I set up the tables and went to tools and couldn't find references. Do I need
to go somewhere else to find references? "scubadiver" wrote: That is what confused me slightly. So maybe it should be: Locations - Revenue - Classification - ClassGroup - Groups "Revenue" and "ClassGroup" are junction tables tble_group GroupID (PK) tble_classgroup GroupID (FK) ClassID (FK) tble_Class ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) Set up these tables and go to tools - references. Show all the tables and then drag the appropriate fields between the appropriate tables then you can visualise the relationships. For each relationship establish referential integrity. -- http://www.ready4mainstream.ny911truth.org/index.html "Marc" wrote: Thank you for the response. Say I have one classID and it falls into 3 different groups. How do I assign one classID to all 3 groups? "scubadiver" wrote: In fact it should be: Locations - Revenue - Classification - Groups tble_group GroupID (PK) tble_Class GroupID (FK) ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) All IDs should ideally be long integer numbers. -- The 11th day of every month: http://truthaction.org/forum/index.php "Marc" wrote: I’m having trouble on how to design the tables for my database. Currently I have 10 locations and they report their revenue each month by classification. There are 10 different classifications. Now the classifications are also classified into 5 groups and the classifications can be in one or more groups. I would like to establish the groups and just have data fall into the rightful group the classification is in. Not sure how to accomplish this. Any ideas? |
#7
|
|||
|
|||
Groups
I didn't say anything about references. To create the table relations tools - relationships Show all the tables then drag the primary key to the foreign key. -- http://www.ready4mainstream.ny911truth.org/index.html "Marc" wrote: I set up the tables and went to tools and couldn't find references. Do I need to go somewhere else to find references? "scubadiver" wrote: That is what confused me slightly. So maybe it should be: Locations - Revenue - Classification - ClassGroup - Groups "Revenue" and "ClassGroup" are junction tables tble_group GroupID (PK) tble_classgroup GroupID (FK) ClassID (FK) tble_Class ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) Set up these tables and go to tools - references. Show all the tables and then drag the appropriate fields between the appropriate tables then you can visualise the relationships. For each relationship establish referential integrity. -- http://www.ready4mainstream.ny911truth.org/index.html "Marc" wrote: Thank you for the response. Say I have one classID and it falls into 3 different groups. How do I assign one classID to all 3 groups? "scubadiver" wrote: In fact it should be: Locations - Revenue - Classification - Groups tble_group GroupID (PK) tble_Class GroupID (FK) ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) All IDs should ideally be long integer numbers. -- The 11th day of every month: http://truthaction.org/forum/index.php "Marc" wrote: I’m having trouble on how to design the tables for my database. Currently I have 10 locations and they report their revenue each month by classification. There are 10 different classifications. Now the classifications are also classified into 5 groups and the classifications can be in one or more groups. I would like to establish the groups and just have data fall into the rightful group the classification is in. Not sure how to accomplish this. Any ideas? |
#8
|
|||
|
|||
Groups
"scubadiver" wrote in message
... I didn't say anything about references. Actually, you did. In this post you used "relationships", as you no doubt intended in the previous one. To create the table relations tools - relationships Show all the tables then drag the primary key to the foreign key. -- http://www.ready4mainstream.ny911truth.org/index.html "Marc" wrote: I set up the tables and went to tools and couldn't find references. Do I need to go somewhere else to find references? "scubadiver" wrote: That is what confused me slightly. So maybe it should be: Locations - Revenue - Classification - ClassGroup - Groups "Revenue" and "ClassGroup" are junction tables tble_group GroupID (PK) tble_classgroup GroupID (FK) ClassID (FK) tble_Class ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) Set up these tables and go to tools - references. Show all the tables and then drag the appropriate fields between the appropriate tables then you can visualise the relationships. For each relationship establish referential integrity. -- http://www.ready4mainstream.ny911truth.org/index.html "Marc" wrote: Thank you for the response. Say I have one classID and it falls into 3 different groups. How do I assign one classID to all 3 groups? "scubadiver" wrote: In fact it should be: Locations - Revenue - Classification - Groups tble_group GroupID (PK) tble_Class GroupID (FK) ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) All IDs should ideally be long integer numbers. -- The 11th day of every month: http://truthaction.org/forum/index.php "Marc" wrote: I'm having trouble on how to design the tables for my database. Currently I have 10 locations and they report their revenue each month by classification. There are 10 different classifications. Now the classifications are also classified into 5 groups and the classifications can be in one or more groups. I would like to establish the groups and just have data fall into the rightful group the classification is in. Not sure how to accomplish this. Any ideas? |
#9
|
|||
|
|||
Groups
Just testing! LoL -- http://www.ready4mainstream.ny911truth.org/index.html "BruceM" wrote: "scubadiver" wrote in message ... I didn't say anything about references. Actually, you did. In this post you used "relationships", as you no doubt intended in the previous one. To create the table relations tools - relationships Show all the tables then drag the primary key to the foreign key. -- http://www.ready4mainstream.ny911truth.org/index.html "Marc" wrote: I set up the tables and went to tools and couldn't find references. Do I need to go somewhere else to find references? "scubadiver" wrote: That is what confused me slightly. So maybe it should be: Locations - Revenue - Classification - ClassGroup - Groups "Revenue" and "ClassGroup" are junction tables tble_group GroupID (PK) tble_classgroup GroupID (FK) ClassID (FK) tble_Class ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) Set up these tables and go to tools - references. Show all the tables and then drag the appropriate fields between the appropriate tables then you can visualise the relationships. For each relationship establish referential integrity. -- http://www.ready4mainstream.ny911truth.org/index.html "Marc" wrote: Thank you for the response. Say I have one classID and it falls into 3 different groups. How do I assign one classID to all 3 groups? "scubadiver" wrote: In fact it should be: Locations - Revenue - Classification - Groups tble_group GroupID (PK) tble_Class GroupID (FK) ClassID (PK) tble_Revenue ClassID (FK) LocationID (FK) tble_location LocationID (PK) All IDs should ideally be long integer numbers. -- The 11th day of every month: http://truthaction.org/forum/index.php "Marc" wrote: I'm having trouble on how to design the tables for my database. Currently I have 10 locations and they report their revenue each month by classification. There are 10 different classifications. Now the classifications are also classified into 5 groups and the classifications can be in one or more groups. I would like to establish the groups and just have data fall into the rightful group the classification is in. Not sure how to accomplish this. Any ideas? |
#10
|
|||
|
|||
Groups
"Marc" wrote in message ... I'm having trouble on how to design the tables for my database. Currently I have 10 locations and they report their revenue each month by classification. There are 10 different classifications. Now the classifications are also classified into 5 groups and the classifications can be in one or more groups. I would like to establish the groups and just have data fall into the rightful group the classification is in. Not sure how to accomplish this. Any ideas? I agree with scubadiver that it sounds like you need junction tables, but some things are unclear, at least to me. Are the ten locations subsets of something else (are they branches of the company, or what exactly)? Are the classifications used by all locations, or does each have its own, or both? How do the classification groups fit into the picture? Some sample data may help make this more understandable. If each location can report revenue from any of ten classifications, and if each classification can be associated with several locations, there is a many-to-many relationship between classifications and locations, so a junction table is needed to resolve the relationship. If each classification can be in several groups, and each group can have several associated classifications, again there is a many-to-many relationship, this time between groups and classifications. Another junction table is needed. Once this is clearly defined, you can start considering how the user will interact with the data. For instance, will they select a classification or a group when reporting revenue? |
Thread Tools | |
Display Modes | |
|
|