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
|
|||
|
|||
linking tables for maximum efficiency
Hi,
I am trying to build two tables containing information about fish that we have collected: tbl Fish: FishPK (one for each individual fish; autonumber) CollectionPK (links to tbl Collection which contains information about where and when we collected the fish) FishName FishAge FishSex FishLength tbl FishAnalysis: FishPK Analysis1 Analysis2 Analysis3 Analysis4 The chemical analyses are only performed on a subset of the entire fish collection. I am pretty happy with this design, but am open to suggestions! In particular, I want to know if this design allows me to: 1. build a query to find the individual fish that have (or have not) been used for chemical analyses 2. build a form for entering results of the analyses - when I receive the data, it looks like this Collection ID (not PK) FishName Analysis1 Analysis2 Analysis3 Analysis4 - which means that I have to collect data from two separate tables before adding the new results to tbl FishAnalysis. How do I build a form to simplify this process? And is my design correct to enable my form? I would appreciate any input! Thanks, buggirl |
#2
|
|||
|
|||
linking tables for maximum efficiency
This part will cause you grief:
Analysis1 Analysis2 Analysis3 Analysis4 What happens when someone decides to add a 5th Analysis? All your forms, reports, and queried based on this table will need to be modified. You need this table to be different and add another table. tbl FishAnalysis: FishAnalysisPK (every table needs a PK even if just an autonumber) FishPK FK to Fish AnalysisPK (FK to the Analysis table!) ChemoResult tbl Analysis AnalysisPK AnalysisName AnalysisNotes This way you can add more chemical analysis in the future. Queries will be much simpler to although you'll need to join in an extra table and remember to do things like left or right joins to see all results. BTW: An old buddy of mine, Jim Widlak, does work in a field like your database suggests in Tennessee. A very tall guy and about 58 years old. Know him? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "buggirl" wrote: Hi, I am trying to build two tables containing information about fish that we have collected: tbl Fish: FishPK (one for each individual fish; autonumber) CollectionPK (links to tbl Collection which contains information about where and when we collected the fish) FishName FishAge FishSex FishLength tbl FishAnalysis: FishPK Analysis1 Analysis2 Analysis3 Analysis4 The chemical analyses are only performed on a subset of the entire fish collection. I am pretty happy with this design, but am open to suggestions! In particular, I want to know if this design allows me to: 1. build a query to find the individual fish that have (or have not) been used for chemical analyses 2. build a form for entering results of the analyses - when I receive the data, it looks like this Collection ID (not PK) FishName Analysis1 Analysis2 Analysis3 Analysis4 - which means that I have to collect data from two separate tables before adding the new results to tbl FishAnalysis. How do I build a form to simplify this process? And is my design correct to enable my form? I would appreciate any input! Thanks, buggirl |
#3
|
|||
|
|||
linking tables for maximum efficiency
Thanks for your reply!
You know, I'm not too worried about adding a fifth analysis. I may not have explained the data clearly enough. All of the analyses are conducted simultaneously and so all of the results come together, as a set. If I did another analysis, it would be conducted on different fish (as the current one destroys the fish), so I would have to construct a separate table. (Otherwise I would have a bunch of blank cells.) Does this make sense? And, if that's the case, am I doing the right thing?! Sorry, I don't know Widlack! I'm primarily a bug ecologist, and I'm just learning about fish! Thanks again! "Jerry Whittle" wrote: This part will cause you grief: Analysis1 Analysis2 Analysis3 Analysis4 What happens when someone decides to add a 5th Analysis? All your forms, reports, and queried based on this table will need to be modified. You need this table to be different and add another table. tbl FishAnalysis: FishAnalysisPK (every table needs a PK even if just an autonumber) FishPK FK to Fish AnalysisPK (FK to the Analysis table!) ChemoResult tbl Analysis AnalysisPK AnalysisName AnalysisNotes This way you can add more chemical analysis in the future. Queries will be much simpler to although you'll need to join in an extra table and remember to do things like left or right joins to see all results. BTW: An old buddy of mine, Jim Widlak, does work in a field like your database suggests in Tennessee. A very tall guy and about 58 years old. Know him? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. |
#4
|
|||
|
|||
linking tables for maximum efficiency
One good rule to follow about database design is to build for easy expansion.
You may only have 4 analysis now and maybe forever, but there's heck to pay the day someone adds a 5th one. I've learned and relearned this hard lesson more than once especially after a customer swore that it would never change. Now it is possible to go overboard. Take phone numbers for example. Back in the day an employee had A phone number. Then someone added Fax numbers. Then the Home phone numbers. Then pager numbers. Now Cell phone numbers. You could make a case that all these phone numbers should be in a seperate table instead of all in different fields in the Employee table. In fact I have a database set up like that. However it's often easier to de-normalize the database just a little in cases such as phone numbers. It's up to you. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "buggirl" wrote: Thanks for your reply! You know, I'm not too worried about adding a fifth analysis. I may not have explained the data clearly enough. All of the analyses are conducted simultaneously and so all of the results come together, as a set. If I did another analysis, it would be conducted on different fish (as the current one destroys the fish), so I would have to construct a separate table. (Otherwise I would have a bunch of blank cells.) Does this make sense? And, if that's the case, am I doing the right thing?! Sorry, I don't know Widlack! I'm primarily a bug ecologist, and I'm just learning about fish! Thanks again! "Jerry Whittle" wrote: This part will cause you grief: Analysis1 Analysis2 Analysis3 Analysis4 What happens when someone decides to add a 5th Analysis? All your forms, reports, and queried based on this table will need to be modified. You need this table to be different and add another table. tbl FishAnalysis: FishAnalysisPK (every table needs a PK even if just an autonumber) FishPK FK to Fish AnalysisPK (FK to the Analysis table!) ChemoResult tbl Analysis AnalysisPK AnalysisName AnalysisNotes This way you can add more chemical analysis in the future. Queries will be much simpler to although you'll need to join in an extra table and remember to do things like left or right joins to see all results. BTW: An old buddy of mine, Jim Widlak, does work in a field like your database suggests in Tennessee. A very tall guy and about 58 years old. Know him? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. |
#5
|
|||
|
|||
linking tables for maximum efficiency
On Wed, 13 Jan 2010 13:25:01 -0800, Jerry Whittle
wrote: One good rule to follow about database design is to build for easy expansion. You may only have 4 analysis now and maybe forever, but there's heck to pay the day someone adds a 5th one. I've learned and relearned this hard lesson more than once especially after a customer swore that it would never change. Yes, and even if you're not worried about future expansion (I've been in this business too long not to worry about it then you still should consider that querying 4 different fields in 1 record is always harder than querying 1 field in 4 records. Databases lend themselves to sets of records, not sets of fields. Armen Stein Microsoft Access MVP www.JStreetTech.com |
Thread Tools | |
Display Modes | |
|
|