Newbie design questions

[Guest]

This forum’s been a big help already, and I haven’t even posted any questions
yet! For the first two questions I was going to ask, actually typing them
out made a solution pop into my head.

I’m working on consolidating a bunch of Excel spreadsheets into an Access
database. I have two general design questions:

1) Is it correct to say that any value that can be calculated from fields
already in the table does not deserve its own field in that table, and that
it should only be present in forms/queries/reports? For instance, if there’s
a “Date Received†field in the table there shouldn’t be a “Fiscal Year
Received†field as well?

2) From a design standpoint, is there any limit to the number of fields per
record before I should start thinking about splitting the data into two
tables? I have a lot of information for each record, even after eliminating
some of the redundant fields from the spreadsheets I’m consolidating, but it
is all unique to the individual record. So my instinct is to keep it all in
the same table, no matter how many fields I have to add. Is this correct?

Thanks.

 

[Keith Wilby]

This forum's been a big help already, and I haven't even posted any
questions
yet! For the first two questions I was going to ask, actually typing them
out made a solution pop into my head.

I'm working on consolidating a bunch of Excel spreadsheets into an Access
database. I have two general design questions:

1) Is it correct to say that any value that can be calculated from fields
already in the table does not deserve its own field in that table, and
that
it should only be present in forms/queries/reports? For instance, if
there's
a "Date Received" field in the table there shouldn't be a "Fiscal Year
Received" field as well?

If a value can be calculated then it should not be stored.

2) From a design standpoint, is there any limit to the number of fields
per
record before I should start thinking about splitting the data into two
tables? I have a lot of information for each record, even after
eliminating
some of the redundant fields from the spreadsheets I'm consolidating, but
it
is all unique to the individual record. So my instinct is to keep it all
in
the same table, no matter how many fields I have to add. Is this correct?

Each table should represent an entity and contain little or no duplication.
Take a look at the Northwinds db tables for an example. Note, there are a
lot of design flaws in that app, but the table/entity thing is represented
quite well IMO.

Regards,
Keith.
www.keithwilby.com

 

[Guest]

This forum’s been a big help already, and I haven’t even posted any questions
yet! For the first two questions I was going to ask, actually typing them
out made a solution pop into my head.

I’m working on consolidating a bunch of Excel spreadsheets into an Access
database. I have two general design questions:

1) Is it correct to say that any value that can be calculated from fields
already in the table does not deserve its own field in that table, and that
it should only be present in forms/queries/reports? For instance, if there’s
a “Date Received†field in the table there shouldn’t be a “Fiscal Year
Received†field as well?

Absolutely Yes! You get a gold star for figuring out one of the basic
principals of good database design. Never carry a calculated value. It
waste space, takes time, and is prone to get out of sync and become incorrect.

2) From a design standpoint, is there any limit to the number of fields per
record before I should start thinking about splitting the data into two
tables? I have a lot of information for each record, even after eliminating
some of the redundant fields from the spreadsheets I’m consolidating, but it
is all unique to the individual record. So my instinct is to keep it all in
the same table, no matter how many fields I have to add. Is this correct?

There is a physical limit of 255 fields to a table. The only reasons to
split data off to a related table is if either redundancy will exist within
the record, which means you need a child table, or if the exact same data is
used by multiple records in your table.