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
|
|||
|
|||
One table or a couple?
Okay, I have a question about denormalizing. I have an activities table.
One particular activity that may take place is the setting of traps, each of which has many captures. Another activity is hunting, which may also have many captures. The problem I'm running into is whether I should have multiple tables to account for the hierarchy or just combine it. Whether it is a hunt or a trap, the data on the capture is relatively the same, but it is necessary that I know (if it is a trapping activity) from which trap the capture it came. Here's one possible structure, which will require a later union query if I want to find all captures: tblActivities ActivityID PK (Counter) Activity_Date (Date) Activity_TypeID (Long) .... tblTrapChecks TrapCheckID (Counter) ActivityID FK (Long, from tblActivities) TrapID (Long) Baited (Boolean) .... tblTrapCaptures TrapCaptureID (Counter) TrapCheckID (Long, from tblTrapChecks) AnimalID (Long) SexID (Long) Lbs (Currency) AgeClassID (Long) ColorID (Long) .... tblHuntCaptures HuntCaptureID (Counter) AnimalID (Long) Quantity (Long) SexID (Long) ..... Or, I could combine the hunt and trap capture tables and leave some fields null, for example: tblAllCaptures CaptureID (Counter) ActivityID (FK Long, Null in the case of a trapping capture) TrapCheckID (FK Long, Null in the case of a hunt capture) AnimalID Quantity SexID Lbs .... Can I get some advice on this? One table, two, or something else I might have overlooked. I could be looking at this all wrong; I am still learning about normalization. It would make sense to combine them so that I just have one captures table, but I think that'll void referential integrity. On the other hand, it might be annoying to have to union the data to bring it all together. AK |
#2
|
|||
|
|||
One table or a couple?
look for the data that is common to both tables tblTrapCaptures and
tblHuntCaptures. put that data into a single table, as tblAllCaptures. the capture-type-specific data would remain in tblTrapCaptures and tblHuntCaptures, as child tables that each have a one-to-one relationship with tblAllCaptures. hth "accesskastle" wrote in message ... Okay, I have a question about denormalizing. I have an activities table. One particular activity that may take place is the setting of traps, each of which has many captures. Another activity is hunting, which may also have many captures. The problem I'm running into is whether I should have multiple tables to account for the hierarchy or just combine it. Whether it is a hunt or a trap, the data on the capture is relatively the same, but it is necessary that I know (if it is a trapping activity) from which trap the capture it came. Here's one possible structure, which will require a later union query if I want to find all captures: tblActivities ActivityID PK (Counter) Activity_Date (Date) Activity_TypeID (Long) ... tblTrapChecks TrapCheckID (Counter) ActivityID FK (Long, from tblActivities) TrapID (Long) Baited (Boolean) ... tblTrapCaptures TrapCaptureID (Counter) TrapCheckID (Long, from tblTrapChecks) AnimalID (Long) SexID (Long) Lbs (Currency) AgeClassID (Long) ColorID (Long) ... tblHuntCaptures HuntCaptureID (Counter) AnimalID (Long) Quantity (Long) SexID (Long) .... Or, I could combine the hunt and trap capture tables and leave some fields null, for example: tblAllCaptures CaptureID (Counter) ActivityID (FK Long, Null in the case of a trapping capture) TrapCheckID (FK Long, Null in the case of a hunt capture) AnimalID Quantity SexID Lbs ... Can I get some advice on this? One table, two, or something else I might have overlooked. I could be looking at this all wrong; I am still learning about normalization. It would make sense to combine them so that I just have one captures table, but I think that'll void referential integrity. On the other hand, it might be annoying to have to union the data to bring it all together. AK |
#3
|
|||
|
|||
One table or a couple?
"tina" wrote:
look for the data that is common to both tables tblTrapCaptures and tblHuntCaptures. put that data into a single table, as tblAllCaptures. the capture-type-specific data would remain in tblTrapCaptures and tblHuntCaptures, as child tables that each have a one-to-one relationship with tblAllCaptures. I disagree. Only once have I ever used a one to one relationship. And that was because I had almost run out of fields on a table. And yes it was a fully normalized table. This is also a pain to setup as far as subforms and such as well. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#4
|
|||
|
|||
One table or a couple?
since MVP Tony Toews recommends against my suggested setup, i'd say put all
the captures in one table, leaving empty fields in each record where the data is not applicable for that capture type. i wouldn't build two separate capture tables, as doing that wuld mean that you're storing data in table names ("hunt" captures and "trap" captures). hth "tina" wrote in message ... look for the data that is common to both tables tblTrapCaptures and tblHuntCaptures. put that data into a single table, as tblAllCaptures. the capture-type-specific data would remain in tblTrapCaptures and tblHuntCaptures, as child tables that each have a one-to-one relationship with tblAllCaptures. hth "accesskastle" wrote in message ... Okay, I have a question about denormalizing. I have an activities table. One particular activity that may take place is the setting of traps, each of which has many captures. Another activity is hunting, which may also have many captures. The problem I'm running into is whether I should have multiple tables to account for the hierarchy or just combine it. Whether it is a hunt or a trap, the data on the capture is relatively the same, but it is necessary that I know (if it is a trapping activity) from which trap the capture it came. Here's one possible structure, which will require a later union query if I want to find all captures: tblActivities ActivityID PK (Counter) Activity_Date (Date) Activity_TypeID (Long) ... tblTrapChecks TrapCheckID (Counter) ActivityID FK (Long, from tblActivities) TrapID (Long) Baited (Boolean) ... tblTrapCaptures TrapCaptureID (Counter) TrapCheckID (Long, from tblTrapChecks) AnimalID (Long) SexID (Long) Lbs (Currency) AgeClassID (Long) ColorID (Long) ... tblHuntCaptures HuntCaptureID (Counter) AnimalID (Long) Quantity (Long) SexID (Long) .... Or, I could combine the hunt and trap capture tables and leave some fields null, for example: tblAllCaptures CaptureID (Counter) ActivityID (FK Long, Null in the case of a trapping capture) TrapCheckID (FK Long, Null in the case of a hunt capture) AnimalID Quantity SexID Lbs ... Can I get some advice on this? One table, two, or something else I might have overlooked. I could be looking at this all wrong; I am still learning about normalization. It would make sense to combine them so that I just have one captures table, but I think that'll void referential integrity. On the other hand, it might be annoying to have to union the data to bring it all together. AK |
#5
|
|||
|
|||
One table or a couple?
"Tony Toews [MVP]" wrote in
: Only once have I ever used a one to one relationship. And that was because I had almost run out of fields on a table. And yes it was a fully normalized table. This is also a pain to setup as far as subforms and such as well. I've used 1:1 tables a lot. In one app that tracks psychiatric cases, the table about the case has a 1:1 side table that holds the demographics, which are collected only when the case is closed. No subform is needed -- the table is joined with a left join in the form's recordsource, and the tab with the demographic fields is not displayed until the main case record is marked as closed. Works pretty well. Another less successful use of 1:1 tables was in an app where I used 3 side tables to sub-type a main record type. Basically, the main table was a "comments" table, but there were three very specific classes of comments, and the side tables included the fields specific to the particular subtypes. This worked well in terms of modelling the data, but not so well in terms of performance, as in several cases it required 3 left joins in a single recordsource (the particular one displayed all the comments in a single continuous subform, with some information from the subtype tables), and that was a huge, huge performance drain. But that was a Jet back end and I always suspected that with a server back end, it would not have been so slow. The app was never completed (the client still uses it, but I don't work for them any longer, and they've done no further development work -- I never got around to proposing a SQL Server upsize to improve performance, partly because I feared that it would cause as many bottlenecks as it removed), so I never got to test that hypothesis. It did teach me that outer joins are *very* expensive in terms of performance in a Jet database and so I've avoided them in future schema designs. The key problem was the requirement for displaying all the records in a single form, with information displayed from the subtype tables. Without that requirement, it wouldn't have been a problem at all. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#6
|
|||
|
|||
One table or a couple?
"tina" wrote:
since MVP Tony Toews recommends against my suggested setup, Well hold on a sec. Just because I disagree doesn't mean I'm right. David brings up some interesting ideas. i'd say put all the captures in one table, leaving empty fields in each record where the data is not applicable for that capture type. i wouldn't build two separate capture tables, as doing that wuld mean that you're storing data in table names ("hunt" captures and "trap" captures). Although I would agree that this solution would likely mean the least amount of work and, possibly, avoiding some kind of "interesting" problem in the future. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#7
|
|||
|
|||
One table or a couple?
David;
I would reccomend that you become knowledgeable with SQL Server before you continue to spew random crap about how SQL Server would cause as many bottlenecks as it removed. Speaking of bottlenecks-- do you see any TPC world records for Jet databases? Now why is it that something with 'more bottlenecks' would handle 100,000 times as many transactions as jet could ever handle? -Aaron On Nov 23, 3:20*pm, "David W. Fenton" wrote: "TonyToews[MVP]" wrote : Only once have I ever used a one to one relationship. And that was because I had almost run out of fields on a table. And yes it was a fully normalized table. This is also a pain to setup as far as subforms and such as well. I've used 1:1 tables a lot. In one app that tracks psychiatric cases, the table about the case has a 1:1 side table that holds the demographics, which are collected only when the case is closed. No subform is needed -- the table is joined with a left join in the form's recordsource, and the tab with the demographic fields is not displayed until the main case record is marked as closed. Works pretty well. Another less successful use of 1:1 tables was in an app where I used 3 side tables to sub-type a main record type. Basically, the main table was a "comments" table, but there were three very specific classes of comments, and the side tables included the fields specific to the particular subtypes. This worked well in terms of modelling the data, but not so well in terms of performance, as in several cases it required 3 left joins in a single recordsource (the particular one displayed all the comments in a single continuous subform, with some information from the subtype tables), and that was a huge, huge performance drain. But that was a Jet back end and I always suspected that with a server back end, it would not have been so slow. The app was never completed (the client still uses it, but I don't work for them any longer, and they've done no further development work -- I never got around to proposing a SQL Server upsize to improve performance, partly because I feared that it would cause as many bottlenecks as it removed), so I never got to test that hypothesis. It did teach me that outer joins are *very* expensive in terms of performance in a Jet database and so I've avoided them in future schema designs. The key problem was the requirement for displaying all the records in a single form, with information displayed from the subtype tables. Without that requirement, it wouldn't have been a problem at all. -- David W. Fenton * * * * * * * * *http://www.dfenton.com/ usenet at dfenton dot com * *http://www.dfenton.com/DFA/ |
#8
|
|||
|
|||
One table or a couple?
I actually think that 1:1 tables are probbaly the most under-utilized
structure available to anyone. Of course-- 1 of the tables has to really be a master-- or else it gets to be a pain in the butt-- but 1:1 tables are a great way to increase performance (because you can fit a lot more records in a page of data). Table Design 1 = Columns 1,2,3,4,5,6,7,8,9,10,11 (20 records per page) Column1 = Primary Key Column2 = Some other Key Thus, if the accounting department only ever looks at columns 3,4,5 and HR only ever looks at columns 6, 7, 8 and purchasing only ever looks at columns 9,10,11-- then yes-- all three departments would get a LOT better performance by organizing three tables 1,2,3,4,5 (40 records per page?) 1,2,6,7,8 (40 records per page?) 1,2,9,10,11 (60 records per page?) Sure, it might cost marginally more storage-- but then again, you guys don't give a crap about storage space because you use a database that handcuffs you with crappy datatypes. If you guys knew anything about optimizing tables you'd use an efficient db. -Aaron On Nov 23, 1:08*pm, "Tony Toews [MVP]" wrote: "tina" wrote: look for the data that is common to both tables tblTrapCaptures and tblHuntCaptures. put that data into a single table, as tblAllCaptures. the capture-type-specific data would remain in tblTrapCaptures and tblHuntCaptures, as child tables that each have a one-to-one relationship with tblAllCaptures. I disagree. *Only once have I ever used a one to one relationship. And that was because I had almost run out of fields on a table. *And yes it was a fully normalized table. This is also a pain to setup as far as subforms and such as well. Tony --TonyToews, Microsoft Access MVP * *Please respond only in the newsgroups so that others can read the entire thread of messages. * *Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm * *Tony'sMicrosoft Access Blog -http://msmvps.com/blogs/access/ |
#9
|
|||
|
|||
One table or a couple?
Thanks all for your comments. Doesn't really sound like consensus, but I'm
going to go ahead with the one table for captures along tina's lines because it is easier, and because it is the same type of information except for the foreign key. AK "Tony Toews [MVP]" wrote: "tina" wrote: since MVP Tony Toews recommends against my suggested setup, Well hold on a sec. Just because I disagree doesn't mean I'm right. David brings up some interesting ideas. i'd say put all the captures in one table, leaving empty fields in each record where the data is not applicable for that capture type. i wouldn't build two separate capture tables, as doing that wuld mean that you're storing data in table names ("hunt" captures and "trap" captures). Although I would agree that this solution would likely mean the least amount of work and, possibly, avoiding some kind of "interesting" problem in the future. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#10
|
|||
|
|||
One table or a couple?
"Tony Toews [MVP]" wrote in
: David brings up some interesting ideas. Another app I used 1:1 was a medical study data collection app in which data on all the kidney transplant patients from a particular hospital was received from the UNOS national transplant registry. These were imported into one table. If one of those patients enrolled in the study, a 1:1 record was created in another table with all the information needed for the study that was not already included in the UNOS data (and, yes, it was a non-normalized data structure and turned out to be a huge problem when 3 years in, before the app was even in production use, UNOS changed their data structure!). In a case where you're getting part of your data from one source and need to maintain that, and you also need to add other data for some (but not all) of the records in the main table, a 1:1 structure is very useful. It also had the advantage in that case that an inner join gave you all the enrolled patients without needing to select on, say, enrollment date Is Not Null. That app should have been designed as a proper survey structure, but it did teach me that when you're importing from one source and adding data from another, it's a perfectly viable structure. I get asked all the time to work on Access apps that draw part of their data from a website, and when the Access app needs to track information different from the website, it can be a pretty useful structure. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
|
Thread Tools | |
Display Modes | |
|
|