1 big table or 1 to 1 relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a rule as to when it is better to use 1 to one relationships between
tables as opposed to using 1 big table? they seem to be the same thing.

I tried to make many 1 to 1 relationship tables in a customer database to
keep things more organized but this seems to make form design harder by
requiring subforms. Is there a better way?
 
Hi, BP.

The only time I use a 1-to-1 relationship is for security reasons, such as
an Employee file, where I'd like to give different permissions to edit the
basic personnel file and more sensitive information.

But, if you have other reasons for splitting the data into more than one
table, you don't have to resort to subforms. Just bring the two tables
together in a query, and base your form on that. It will be just as if they
were a single table.

Hope that helps.
Sprinks
 
So, is it better to ride a bicycle, or fly a 747? <g!> Wouldn't that depend
on: what you want to accomplish? what skills you already possess? what
equipment you have access to? ...?

What are you trying to accomplish? How have you come to the conclusion that
your data is related 1:1? Why are you considering choosing wide or multiple
table(s)? What kind of data are we talking about?

You haven't described what the underlying entity-relationship model is.

Sprinks points out that a 1:1 table relationship can be used to provide
security (and there are other approaches, if this is your goal).

Another reason for 1:1 tables is termed "sub-typing", where all rows share a
common set of attributes, but some "sub-types" also have unique attributes
you elect not to keep in a single table.

Some folks design their tables ("big", "wide") to match what they have been
using in a spreadsheet. This is a serious mistake when you are using a
relational database (e.g., Access). You will likely not be able to make
easy use of the tools and functions that Access provides if your table is
actually a copy of a spreadsheet.

Provide the 'group a bit more detail if you'd like a bit more detailed
suggestions...
 
Thank you,

It sounds like I can make it one table.

I tried to use a query like you described to combine the data, but it
wouldn't let me enter new data, at least in the datasheet view of the query.
Would it work better in a form or would I have to use dynaset (inconsitent)?
 
Is there a rule as to when it is better to use 1 to one relationships between
tables as opposed to using 1 big table? they seem to be the same thing.

I tried to make many 1 to 1 relationship tables in a customer database to
keep things more organized but this seems to make form design harder by
requiring subforms. Is there a better way?
Click to expand...

Neither.

One to one relationships are *occasionally* useful (in Subclassing, or
if you want field-level security). But if you're using one to ones to
get around the 255 field limit, then the problem lies in your table
normalization!

I've needed as many as 60 fields in a table (and dealt with a few
more); but I am not aware of ANY properly normalized table with more
than a hundred fields. "Requiring Subforms" is not a disadvantage -
it's a recognition of the reality that your data contains one-to-many
relationships! USE the tools that Access provides to use one to *MANY*
relationships, rather than embedding one to many relationships within
each record of your table.

I may be blowing smoke here, but please, post a description of your
wide table. I'll be very surprised if it isn't going to be better
handled as two or more tables in a one to many relationship.

John W. Vinson[MVP]
 
Back
Top