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
|
|||
|
|||
The Biggest Design Problem - HELPPPPP!
I need help with this design issue. My goal is to try to have the database
size as minimal as possible. This is a simple example for what I need. I need to have a way to enter 10 officers positions for a Company, its Divisions and its Stores. There will be 178 terms of office (1921-2099). I will need 10 officer positions posted to each term for the Company, each of the 52 Divisions, and then the multiple stores under each Division. Depending on the design, the Company could either produce 1,780 records or 178 records. Example A: the 1,780 records, (10 officers x 178 terms), design that table would look like: CompanyOfficers (table) COID (PK) TermID (FK) OfficerID (FK) EmployeeID (FK) Example B: the 178 records, (10 officers all in one record), the table would look something like: CompanyOfficers (table) COID (PK) TermID (FK) Officer1ID (FK) (which would really be the EmployeeID) Officer2ID (FK) (which would really be the EmployeeID) Officer3ID (FK) (which would really be the EmployeeID) Officer3ID (FK) (which would really be the EmployeeID) Officer4ID (FK) (which would really be the EmployeeID) Officer5ID (FK) (which would really be the EmployeeID) Officer6ID (FK) (which would really be the EmployeeID) Officer7ID (FK) (which would really be the EmployeeID) Officer8ID (FK) (which would really be the EmployeeID) Officer9ID (FK) (which would really be the EmployeeID) Officer10ID (FK) (which would really be the EmployeeID) The Example B works fine in the screen maintenance and entry. The problem I'm having is creating subform on the Employee form that lists every "office" which that employee is held. The "JOINS" are not working out. Example A is the most correct design, but the problem exists when creating these for the 52 Divisions and hundreds of Stores in each Division. The Divisions alone under Example A would generate 92,560 records (52 divisions x 10 officers x 178 terms). There are currently 467 Stores, hence Example A would now generate 831,260 records (467 x 10 officers x 178 terms); and Example B would generate 83,126 records (467 x 10 officers all in one table) How would any of you resolve this "design issue"? I really welcome your thoughts. Obviously, Example B would be preferred, but I don't know how to make the multiple "self-joins" -- I think that's what they are called. Please, I call upon all of the ACCESS EXPERTS to help. Thank you for your time. |
#2
|
|||
|
|||
The Biggest Design Problem - HELPPPPP!
Your goal should not be to have the database size as minimal as possible.
Rather it should be to have the database work as well as possible. Don't worry about the number of records. Rather worry about that report which you need actually producing the correct results in a timely manner plus an eye towards flexibility if someone adds another officer or two. Don't even think about doing it the Table B way. You say 10 officers and have 10 officers across in the table (like a spreadsheet). What happens to all your forms, reports, and queries when someone says there are now 11 officers? Don't say it won't happen. Can any of these officers be at more than one store or division? Can an officer ber in more than one year/Term? In that case you will also need some joining or linking tables to break up the Many-to-Many relationships. Why do you need both the Employee ID and Officer ID in the table? Seems redundent to me. The way I see it you need at least the following tables. If there are any M-M relationships, you will also need some linking or bridging tables. Officers Table Term Table Division Table Store Table. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "laskowv" wrote: I need help with this design issue. My goal is to try to have the database size as minimal as possible. This is a simple example for what I need. I need to have a way to enter 10 officers positions for a Company, its Divisions and its Stores. There will be 178 terms of office (1921-2099). I will need 10 officer positions posted to each term for the Company, each of the 52 Divisions, and then the multiple stores under each Division. Depending on the design, the Company could either produce 1,780 records or 178 records. Example A: the 1,780 records, (10 officers x 178 terms), design that table would look like: CompanyOfficers (table) COID (PK) TermID (FK) OfficerID (FK) EmployeeID (FK) Example B: the 178 records, (10 officers all in one record), the table would look something like: CompanyOfficers (table) COID (PK) TermID (FK) Officer1ID (FK) (which would really be the EmployeeID) Officer2ID (FK) (which would really be the EmployeeID) Officer3ID (FK) (which would really be the EmployeeID) Officer3ID (FK) (which would really be the EmployeeID) Officer4ID (FK) (which would really be the EmployeeID) Officer5ID (FK) (which would really be the EmployeeID) Officer6ID (FK) (which would really be the EmployeeID) Officer7ID (FK) (which would really be the EmployeeID) Officer8ID (FK) (which would really be the EmployeeID) Officer9ID (FK) (which would really be the EmployeeID) Officer10ID (FK) (which would really be the EmployeeID) The Example B works fine in the screen maintenance and entry. The problem I'm having is creating subform on the Employee form that lists every "office" which that employee is held. The "JOINS" are not working out. Example A is the most correct design, but the problem exists when creating these for the 52 Divisions and hundreds of Stores in each Division. The Divisions alone under Example A would generate 92,560 records (52 divisions x 10 officers x 178 terms). There are currently 467 Stores, hence Example A would now generate 831,260 records (467 x 10 officers x 178 terms); and Example B would generate 83,126 records (467 x 10 officers all in one table) How would any of you resolve this "design issue"? I really welcome your thoughts. Obviously, Example B would be preferred, but I don't know how to make the multiple "self-joins" -- I think that's what they are called. Please, I call upon all of the ACCESS EXPERTS to help. Thank you for your time. |
#3
|
|||
|
|||
The Biggest Design Problem - HELPPPPP!
First, I think my concern over database size is just. Using Example A for
the Stores will eventually generate over 800,000 records in one table. I already have 100,000 in just the Employee table. This gets even more complicated with "committees"; which there are about 35 positions which will eventually generate almost 1.5 million records. I will design the committees the same way the officers are designed; but first I have to get through the officers issue. I am really worried about performance issues and making the database too large that will cause it to error. The OfficerID is that actual position (President, Treasurer, Vice President, etc...); and yes, I already have a Term table. The officers can hold both store and division officers positions within the same term. I want to have the ALL of the officer positions show on the form and then a combo box for the administrator to just select the EmployeeID holding that office. This way, a "blank" position is very visible on the screen. I guess a "continous form" would the way to go here? Thanks. "Jerry Whittle" wrote: Your goal should not be to have the database size as minimal as possible. Rather it should be to have the database work as well as possible. Don't worry about the number of records. Rather worry about that report which you need actually producing the correct results in a timely manner plus an eye towards flexibility if someone adds another officer or two. Don't even think about doing it the Table B way. You say 10 officers and have 10 officers across in the table (like a spreadsheet). What happens to all your forms, reports, and queries when someone says there are now 11 officers? Don't say it won't happen. Can any of these officers be at more than one store or division? Can an officer ber in more than one year/Term? In that case you will also need some joining or linking tables to break up the Many-to-Many relationships. Why do you need both the Employee ID and Officer ID in the table? Seems redundent to me. The way I see it you need at least the following tables. If there are any M-M relationships, you will also need some linking or bridging tables. Officers Table Term Table Division Table Store Table. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "laskowv" wrote: I need help with this design issue. My goal is to try to have the database size as minimal as possible. This is a simple example for what I need. I need to have a way to enter 10 officers positions for a Company, its Divisions and its Stores. There will be 178 terms of office (1921-2099). I will need 10 officer positions posted to each term for the Company, each of the 52 Divisions, and then the multiple stores under each Division. Depending on the design, the Company could either produce 1,780 records or 178 records. Example A: the 1,780 records, (10 officers x 178 terms), design that table would look like: CompanyOfficers (table) COID (PK) TermID (FK) OfficerID (FK) EmployeeID (FK) Example B: the 178 records, (10 officers all in one record), the table would look something like: CompanyOfficers (table) COID (PK) TermID (FK) Officer1ID (FK) (which would really be the EmployeeID) Officer2ID (FK) (which would really be the EmployeeID) Officer3ID (FK) (which would really be the EmployeeID) Officer3ID (FK) (which would really be the EmployeeID) Officer4ID (FK) (which would really be the EmployeeID) Officer5ID (FK) (which would really be the EmployeeID) Officer6ID (FK) (which would really be the EmployeeID) Officer7ID (FK) (which would really be the EmployeeID) Officer8ID (FK) (which would really be the EmployeeID) Officer9ID (FK) (which would really be the EmployeeID) Officer10ID (FK) (which would really be the EmployeeID) The Example B works fine in the screen maintenance and entry. The problem I'm having is creating subform on the Employee form that lists every "office" which that employee is held. The "JOINS" are not working out. Example A is the most correct design, but the problem exists when creating these for the 52 Divisions and hundreds of Stores in each Division. The Divisions alone under Example A would generate 92,560 records (52 divisions x 10 officers x 178 terms). There are currently 467 Stores, hence Example A would now generate 831,260 records (467 x 10 officers x 178 terms); and Example B would generate 83,126 records (467 x 10 officers all in one table) How would any of you resolve this "design issue"? I really welcome your thoughts. Obviously, Example B would be preferred, but I don't know how to make the multiple "self-joins" -- I think that's what they are called. Please, I call upon all of the ACCESS EXPERTS to help. Thank you for your time. |
#4
|
|||
|
|||
The Biggest Design Problem - HELPPPPP!
First, I think my concern over database size is just. Using Example A for
the Stores will eventually generate over 800,000 records in one table. I already have 100,000 in just the Employee table. This gets even more complicated with "committees"; which there are about 35 positions which will eventually generate almost 1.5 million records. I will design the committees the same way the officers are designed; but first I have to get through the officers issue. I am really worried about performance issues and making the database too large that will cause it to error. The OfficerID is that actual position (President, Treasurer, Vice President, etc...); and yes, I already have a Term table. The officers can hold both store and division officers positions within the same term. I want to have the ALL of the officer positions show on the form and then a combo box for the administrator to just select the EmployeeID holding that office. This way, a "blank" position is very visible on the screen. I guess a "continous form" would the way to go here? Thanks. "Jerry Whittle" wrote: Your goal should not be to have the database size as minimal as possible. Rather it should be to have the database work as well as possible. Don't worry about the number of records. Rather worry about that report which you need actually producing the correct results in a timely manner plus an eye towards flexibility if someone adds another officer or two. Don't even think about doing it the Table B way. You say 10 officers and have 10 officers across in the table (like a spreadsheet). What happens to all your forms, reports, and queries when someone says there are now 11 officers? Don't say it won't happen. Can any of these officers be at more than one store or division? Can an officer ber in more than one year/Term? In that case you will also need some joining or linking tables to break up the Many-to-Many relationships. Why do you need both the Employee ID and Officer ID in the table? Seems redundent to me. The way I see it you need at least the following tables. If there are any M-M relationships, you will also need some linking or bridging tables. Officers Table Term Table Division Table Store Table. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "laskowv" wrote: I need help with this design issue. My goal is to try to have the database size as minimal as possible. This is a simple example for what I need. I need to have a way to enter 10 officers positions for a Company, its Divisions and its Stores. There will be 178 terms of office (1921-2099). I will need 10 officer positions posted to each term for the Company, each of the 52 Divisions, and then the multiple stores under each Division. Depending on the design, the Company could either produce 1,780 records or 178 records. Example A: the 1,780 records, (10 officers x 178 terms), design that table would look like: CompanyOfficers (table) COID (PK) TermID (FK) OfficerID (FK) EmployeeID (FK) Example B: the 178 records, (10 officers all in one record), the table would look something like: CompanyOfficers (table) COID (PK) TermID (FK) Officer1ID (FK) (which would really be the EmployeeID) Officer2ID (FK) (which would really be the EmployeeID) Officer3ID (FK) (which would really be the EmployeeID) Officer3ID (FK) (which would really be the EmployeeID) Officer4ID (FK) (which would really be the EmployeeID) Officer5ID (FK) (which would really be the EmployeeID) Officer6ID (FK) (which would really be the EmployeeID) Officer7ID (FK) (which would really be the EmployeeID) Officer8ID (FK) (which would really be the EmployeeID) Officer9ID (FK) (which would really be the EmployeeID) Officer10ID (FK) (which would really be the EmployeeID) The Example B works fine in the screen maintenance and entry. The problem I'm having is creating subform on the Employee form that lists every "office" which that employee is held. The "JOINS" are not working out. Example A is the most correct design, but the problem exists when creating these for the 52 Divisions and hundreds of Stores in each Division. The Divisions alone under Example A would generate 92,560 records (52 divisions x 10 officers x 178 terms). There are currently 467 Stores, hence Example A would now generate 831,260 records (467 x 10 officers x 178 terms); and Example B would generate 83,126 records (467 x 10 officers all in one table) How would any of you resolve this "design issue"? I really welcome your thoughts. Obviously, Example B would be preferred, but I don't know how to make the multiple "self-joins" -- I think that's what they are called. Please, I call upon all of the ACCESS EXPERTS to help. Thank you for your time. |
#5
|
|||
|
|||
The Biggest Design Problem - HELPPPPP!
A properly normalized database will be smaller than one badly normalized. For
example if you have repeating data in a table, the database will be bigger than if the repeating data was split off into another table. An example would be putting in the full name and address of the buyer for each order instead of just a link to a table where the name and address is entered once. If your database is well normalized and you have millions of records, it's very possible that Access is not the right tool for the job. You may need to upsize to something like SQL Server to be able to handle the amount of data and get reports to run in an acceptably fast manner. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "laskowv" wrote: First, I think my concern over database size is just. Using Example A for the Stores will eventually generate over 800,000 records in one table. I already have 100,000 in just the Employee table. This gets even more complicated with "committees"; which there are about 35 positions which will eventually generate almost 1.5 million records. I will design the committees the same way the officers are designed; but first I have to get through the officers issue. I am really worried about performance issues and making the database too large that will cause it to error. The OfficerID is that actual position (President, Treasurer, Vice President, etc...); and yes, I already have a Term table. The officers can hold both store and division officers positions within the same term. I want to have the ALL of the officer positions show on the form and then a combo box for the administrator to just select the EmployeeID holding that office. This way, a "blank" position is very visible on the screen. I guess a "continous form" would the way to go here? Thanks. "Jerry Whittle" wrote: Your goal should not be to have the database size as minimal as possible. Rather it should be to have the database work as well as possible. Don't worry about the number of records. Rather worry about that report which you need actually producing the correct results in a timely manner plus an eye towards flexibility if someone adds another officer or two. Don't even think about doing it the Table B way. You say 10 officers and have 10 officers across in the table (like a spreadsheet). What happens to all your forms, reports, and queries when someone says there are now 11 officers? Don't say it won't happen. Can any of these officers be at more than one store or division? Can an officer ber in more than one year/Term? In that case you will also need some joining or linking tables to break up the Many-to-Many relationships. Why do you need both the Employee ID and Officer ID in the table? Seems redundent to me. The way I see it you need at least the following tables. If there are any M-M relationships, you will also need some linking or bridging tables. Officers Table Term Table Division Table Store Table. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "laskowv" wrote: I need help with this design issue. My goal is to try to have the database size as minimal as possible. This is a simple example for what I need. I need to have a way to enter 10 officers positions for a Company, its Divisions and its Stores. There will be 178 terms of office (1921-2099). I will need 10 officer positions posted to each term for the Company, each of the 52 Divisions, and then the multiple stores under each Division. Depending on the design, the Company could either produce 1,780 records or 178 records. Example A: the 1,780 records, (10 officers x 178 terms), design that table would look like: CompanyOfficers (table) COID (PK) TermID (FK) OfficerID (FK) EmployeeID (FK) Example B: the 178 records, (10 officers all in one record), the table would look something like: CompanyOfficers (table) COID (PK) TermID (FK) Officer1ID (FK) (which would really be the EmployeeID) Officer2ID (FK) (which would really be the EmployeeID) Officer3ID (FK) (which would really be the EmployeeID) Officer3ID (FK) (which would really be the EmployeeID) Officer4ID (FK) (which would really be the EmployeeID) Officer5ID (FK) (which would really be the EmployeeID) Officer6ID (FK) (which would really be the EmployeeID) Officer7ID (FK) (which would really be the EmployeeID) Officer8ID (FK) (which would really be the EmployeeID) Officer9ID (FK) (which would really be the EmployeeID) Officer10ID (FK) (which would really be the EmployeeID) The Example B works fine in the screen maintenance and entry. The problem I'm having is creating subform on the Employee form that lists every "office" which that employee is held. The "JOINS" are not working out. Example A is the most correct design, but the problem exists when creating these for the 52 Divisions and hundreds of Stores in each Division. The Divisions alone under Example A would generate 92,560 records (52 divisions x 10 officers x 178 terms). There are currently 467 Stores, hence Example A would now generate 831,260 records (467 x 10 officers x 178 terms); and Example B would generate 83,126 records (467 x 10 officers all in one table) How would any of you resolve this "design issue"? I really welcome your thoughts. Obviously, Example B would be preferred, but I don't know how to make the multiple "self-joins" -- I think that's what they are called. Please, I call upon all of the ACCESS EXPERTS to help. Thank you for your time. |
Thread Tools | |
Display Modes | |
|
|