Use relationship or not?

  • Thread starter Thread starter Susanne
  • Start date Start date
S

Susanne

I have a very large main table (over 100 fields) that I can easily make into
other "main topic" tables ... that is I can make it into 3 instead of having
just the one. There would only be a 1-1 relationship. Is there ever the
time when this is ok to use?

I've never had a table this big before and it is kind of freaking me out.

Thanks!
 
Susanne said:
I have a very large main table (over 100 fields) that I can easily make into
other "main topic" tables ... that is I can make it into 3 instead of having
just the one. There would only be a 1-1 relationship. Is there ever the
time when this is ok to use?
Click to expand...


Yes, there are times when it is appropriate. However, in
your scenario, it may (or may not) be a good idea. Before
you do anything else, you really need to make sure the table
is properly normalized (100 fields is a LOT of attributes
for a single emtity). If the table is not normalized, then
spreading the fields over multiple tables will probably make
things even more diffcult to work with.
 
It is very unusual to have a table with that many fields in a well-designed
relational database. I suspect that you may have groups of data that should
be broken out into a separate table (perhaps several separate tables).
However, without knowing anything about your table fields that is very
difficult to determine.

If you can split your table into 3 tables then there must be some reasoning
you would use on which fields go into which of the three tables.

Usually when you have a 1 to 1 relationship you are sub-classing the data.
That is you have a group of fields that only apply in certain situations based
on data and in other situations the fields are not applicable.

For example, if you were storing contact information on individuals you might
have professional contacts and personal contacts. On the professional
contacts you might want to store information about the associated businesses
in addition to Name,contact number, etc. For personal contacts, you might
want to store information on birthdates, anniversaries, number of children,
etc. You might then have two subclass tables to store the different types of
information for the different kinds of contacts.

You could, of course, keep all the information in one table. You might run
into a problem with trying to store too much data (2000 character limit per
record) in any single record.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
I have a very large main table (over 100 fields) that I can easily make into
other "main topic" tables ... that is I can make it into 3 instead of having
just the one. There would only be a 1-1 relationship. Is there ever the
time when this is ok to use?

I've never had a table this big before and it is kind of freaking me out.

Thanks!
Click to expand...

I never have either, and it's freaking ME out. I very strongly suspect that
the table is not correctly normalized; I'd guess you have one or more
one-to-many relationships embedded in the table.

What kind of Entity (real-life person, thing, or event) does this table
represent? Could you post some of the fieldnames? I think you do need to split
up the table, but NOT into one-to-one relationships; doing so will make
matters worse not better.
 
It's dealing with something for the military, and yes, there are a lot of
fields that they need to keep info on. Not my decision :) I do have some
one to many relationships where they are appropriate, like keeping an
environment log for each dive (yes, diving) every 30 minutes. There is a
bunch of pieces on each suit they need to keep track of (every single piece,
that is why there are so many fields, Serial #s for each type). I do NOT
have a field for each piece, just each of the mandatory items. They are
being very strict in how they want it to display on the screen (as it does on
paper, a specific layout in a specific order) and so I cannot create a simple
one to many relationship.

So, I have a dive with lots of attributes. This is what I want to split,
the dive suit parts ... what they call pooled and non-pooled parts. It is
all still related to the regular dive info, but I can easily separate it.

I hope that helps and didn't make it more confusing.
 
So it sounds as if you need a table named Parts to

tblParts
PartID (Primary Key - Autonumber)
PartName (Alternative primary key if PartName is always unique)
PartCode (Alternative primary key if Part
DisplayOrder (Number type Double)
Pooled (If this is an attribute of the part Type/ if it is an attribute of a
specific part then the information on Pooled [or not] would go into the table
tblSuitParts)

Then another table to record the parts for each suit and any other information
on each part that you need to record about that specific part.
tblSuitParts
SuitID
PartID
SerialNumber
PartCondition

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
It's dealing with something for the military, and yes, there are a lot of
fields that they need to keep info on. Not my decision :) I do have some
one to many relationships where they are appropriate, like keeping an
environment log for each dive (yes, diving) every 30 minutes. There is a
bunch of pieces on each suit they need to keep track of (every single piece,
that is why there are so many fields, Serial #s for each type). I do NOT
have a field for each piece, just each of the mandatory items. They are
being very strict in how they want it to display on the screen (as it does on
paper, a specific layout in a specific order) and so I cannot create a simple
one to many relationship.

So, I have a dive with lots of attributes. This is what I want to split,
the dive suit parts ... what they call pooled and non-pooled parts. It is
all still related to the regular dive info, but I can easily separate it.
Click to expand...

That certainly sounds like a on (dive) to many (pieces) relationship - or more
likely a many to many, since I presume that a given suit piece will be used on
many dives. The appearance on the screen should certainly NOT drive your
decisions about table structure! I'd even consider using an unbound form with
VBA code to move data to and from normalized tables, before creating a
wide-flat design as you suggest. But it sounds like you have a handle on the
design and can do it either way: one hugely wide table (if it won't push up
against the 2000 bytes per record limit), or a normalized design (my
recommendation).
 
Susanne,

I'm a retired Marine CWO and working out here in 29 Palms CA. I'm now a DOD
civilian and have been designing dB for over 25 years. Your table seems to
me it needs to be normalized. At least to the third normal form. You can
get info from the Internet about how to do this. There are great examples on
the net. I am currently working to fix a dB that has over 50 tables in
relationships. If you understand normalization, then you can easy ask
normalization questions to see if you do infact need all the fields in one
table.

Example. A diver can only where 1 suit at a time. Is this a true statment.
If it is, then you can seperate all the suit info into a table and have a
SuitId to associate the suit to the diver. But each suit can be worn by many
divers.

If you are not familiar with diving, you need to ask your SME's questions to
help you normalize your dB.

Martin Cox
 
Back
Top