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
|
|||
|
|||
Big Tables or One-to-One?
Which is a better design? A huge table with many fields (approx. 66 fields)
or to break that table up into smaller tables and establish one-to-one relationships? If I break it up, how do I create the one-to-one relationship? |
#2
|
|||
|
|||
Big Tables or One-to-One?
There are always exceptions, but, generally speaking, a table with 66
fields is more than likely not properly designed. As far as what type of relationships you would use if you separate the data into multiple tables, that all depends on the data itself. If you can provide some type of idea as to what your data is, someone may be able offer more specific advice. -- _________ Sean Bailey "sljack" wrote: Which is a better design? A huge table with many fields (approx. 66 fields) or to break that table up into smaller tables and establish one-to-one relationships? If I break it up, how do I create the one-to-one relationship? |
#3
|
|||
|
|||
Big Tables or One-to-One?
On Tue, 8 Jul 2008 13:09:06 -0700, sljack
wrote: Which is a better design? A huge table with many fields (approx. 66 fields) or to break that table up into smaller tables and establish one-to-one relationships? If I break it up, how do I create the one-to-one relationship? Almost certainly, the appropriate answer is the Zen "Mu" - "unask the question". What real-life entity has 66 distinct, nonrepeating, non-interdependent attributes? What Entity (real-life person, thing or event) is represented by this table? What are some of the fields? I *suspect* that some of these fields are actually concealed one-to-many relationships, and that you may need multiple tables related one to many, rather than one to one. On the other hand I *have* (reluctantly, dragged kicking and screaming) built a couple of 60-field tables. It may be legitimate. To directly answer your question: if you won't be exceeding the 2000 bytes per record limit (a nasty little "gotcha" since Access lets you create a table with 255 fields each 255 bytes, without complaining until you actually enter more than 2000 bytes into any one record!), go with a single table. To create a one to one relationship give each table a Primary Key and use the relationships window to relate the two tables, primary key to primary key. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Big Tables or One-to-One?
On Tue, 08 Jul 2008 17:08:09 -0600, John W. Vinson
wrote: What real-life entity has 66 distinct, nonrepeating, non-interdependent attributes? What Entity (real-life person, thing or event) is represented by this table? What are some of the fields? I agree with John. We've built many complex databases where no single table had more than a few dozen fields. If you can, go with one table. If you do go with one-to-one tables, your forms, reports and queries will be more complex too, so think about it carefully before you start down that path. Armen Stein Microsoft Access MVP www.JStreetTech.com |
#5
|
|||
|
|||
Big Tables or One-to-One?
The database is going to track legal administrative law cases. I've broken
up the data into the following: tblCaseinfo (currently contains 20 fields) tblRegion (a many-to-one relationship with tblCaseinfo because each Region can have multiple cases). tblRepresentatives (a many-to-one relationship with tblCaseInfo b/c each representative may have multiple cases) tblAction (this is the one-to-one relationship with tblCaseinfo since each case has a specific action, along with date fields relating to when the action was taken, appealed, etc.) tblActnDetails (this is a table that contains information about the type of penalty imposed, amt of penalty, date imposed, etc. This table is a many-to-one relationship with Action because for every action, there can be a multitude of penalties) tblDiscRec - many-to-one with tblCaseinfo tblDiscSent - many-to-onewith tblCaseinfo tblHrgs - one-to-one with tblCaseinfo since each case only has one "trial" (in administrative law they are called "hearings") tblHrgTasks - many-to-one relationship with tblHrgs tblIndex - a many-to-one relationship with tblCaseinfo. It is a 3-field table that logs events for each case. tblStatus - a one-to-one relationship with tblCaseinfo. Each case only has one "status" but I split this off because the tblCaseinfo was becoming too large with too many fields tblDisposition - one-to-one relationship with tblCaseinfo. Again, each case only has one disposition, but I split it b/c of size Here is my dilemma in the design: The first entry of a new case will be in the tblCaseinfo table. When the user enters the details, he will at that time also make entries into the tblAction and tblActnDetails tables b/c each case starts out with that information. But, the case will not necessarily have information entered about its Status or Hearing (the other one-to-one relationships with tblCaseinfo) until a later date. I have found that with the form I have built that if a user begins to enter a new case and gets interrupted and does not enter information about the Action, than the form will not display the new record the user began when it is reopened and the user ends up entering it again, thus creating a duplicate entry. How do I fix this? Here is the query for my form the user uses to enter case information: SELECT tblCaseInfo.*, tblStatus.ClosedDate, tblRegion.ID, tblRegion.SUBOFC, tblRegion.PM, tblRegion.PMPH, tblRegion.Ext, tblRepresentatives.*, tblAction.Actn, tblAction.NoticeDate, tblAction.Imposed, tblAction.Appeal, tblAction.Program, tblAction.Surveys, tblAction.IDROutcome, tblAction.TotalImposedAmt FROM tblRepresentatives RIGHT JOIN (((tblCaseInfo LEFT JOIN tblRegion ON tblCaseInfo.FieldOfc = tblRegion.ID) LEFT JOIN tblStatus ON tblCaseInfo.CaseId = tblStatus.CaseId) INNER JOIN tblAction ON tblCaseInfo.CaseId = tblAction.CaseID) ON tblRepresentatives.ID = tblCaseInfo.RepID; "Armen Stein" wrote: On Tue, 08 Jul 2008 17:08:09 -0600, John W. Vinson wrote: What real-life entity has 66 distinct, nonrepeating, non-interdependent attributes? What Entity (real-life person, thing or event) is represented by this table? What are some of the fields? I agree with John. We've built many complex databases where no single table had more than a few dozen fields. If you can, go with one table. If you do go with one-to-one tables, your forms, reports and queries will be more complex too, so think about it carefully before you start down that path. Armen Stein Microsoft Access MVP www.JStreetTech.com |
Thread Tools | |
Display Modes | |
|
|