design help/how many tables?

[mas]

i'm new to access and have a database design best practice
question.

i'm making a database to hold construction project data.
currently there's nothing that is a one to many
relationship. but there are several logical groups of
data, each w/ several fields. (location, design concepts,
costs, size, etc).

because of the lack of relationships should this only be
one table or should i split it into multiple tables linked
by a project id? ...costs in one table, sizes in another.

other than making the queries a bit more complicated
because of the joins is there any advantages (to having
multiple)? if it is all in one table are there any
performance issues because of the large number of fields?
any other considerations i need to think about?

thanks. any advice/thoughts welcome.
mas

 

[John Vinson]

i'm new to access and have a database design best practice
question.

i'm making a database to hold construction project data.
currently there's nothing that is a one to many
relationship. but there are several logical groups of
data, each w/ several fields. (location, design concepts,
costs, size, etc).

because of the lack of relationships should this only be
one table or should i split it into multiple tables linked
by a project id? ...costs in one table, sizes in another.

other than making the queries a bit more complicated
because of the joins is there any advantages (to having
multiple)? if it is all in one table are there any
performance issues because of the large number of fields?
any other considerations i need to think about?

I'm sure you have LOTS of one to many relationships - until you get
into the relational mindset it can be hard to see them however!

For instance, I'm sure you have a number of "design concepts" which
could be applied to multiple projects, and that each project would use
several "design concepts" - a many to many relationship. This would
imply a table of DesignConcepts, and a third table related one-to-many
to Projects and to DesignConcepts. Similarly, you'll have LOTS of
costs (for the various stages of construction); these should be stored
as records in a table, not as fields.

The key concept is to identify the "Entities" - real-life persons,
things, or events - relevant to your needs. Each kind of Entity should
have its own Table. If you care to post some more descriptions we'll
be glad to help with the structure - but I can say with confidence
that you'll have multiple tables, not just one!

 

[Tim Ferguson]

I am making a database to hold construction project data.
Currently there is nothing that is a one to many
relationship, but there are several logical groups of
data, each with several fields (location, design concepts,
costs, size, etc).

This is not a best practice question: it is a how to do systems analysis
question. Do not think about "logical groups" or "data", but consider what
entities you will be handling. Buildings; Projects; Locations; Bids;
Quotations; CommissioningBodies all spring to mind but you will know your
business much better than I. Each of these should be implemented as a
table, with the relevant relationships having to be worked out next. When
you think you have it all right, go back round again and redo it -- at
least a couple of times. This stage should last for about three-quarters
_or more_ of the design time, because pratting about with a keyboard and
forms code is the easy bit, to be tucked in at the end.

Other than making the queries a bit more complicated
because of the joins is there any advantage to having
multiple? If it is all in one table are there any
performance issues because of the large number of fields?

It is not about performance, or about disk space: db design is all about
accuracy. The price of inadequate normalisation is having two different
prices for the same item, or suppliers who don't exist -- either of which
is much worse than taking an extra 0.7 seconds to see the result of a
query.

Hope that helps


Tim F

 

[mas]

thanks to both for your thoughts. i guess i said there
were no one-to-many relationships because while a building
might have multiple costs, no two buildings will ever have
the same cost (therefore duplicate values). or if they do,
i don't plan on creating a pull down w/ defined ranges to
choose from... just a text box for each project manager to
type whatever number they want to enter. also, it may have
an estimated construction cost, an actual construction
cost, a furniture cost, etc but we're not retaining
multiple furniture costs for each project.

same is true w/ design concepts. i could pre-define a
number of concepts for each project to choose from (thus
creating a table for these records as a lookup list) but i
was planning on just making a memo field for each manager
to describe the project as he/she wanted. there could be
multiple design concepts but they also could all be saved
in paragraph form in a memo field. the purpose of this
database is primarily to archive project data. someone
someday will say 'how big was suchandsuch project'.
they'll look up the project name and see a report w/ all
the info.

so...

if i have a project table with some fields and a cost
table with various fields, and everytime i query the
project, i want to see all the information related to the
project (cost and other) i would always join both tables,
why not then just make one table?

it seems (for my db) the project report would always be
one row of data related to the project. what do i gain by
spliting up the tables?

please know that i'm not trying to sound opposed to doing
this or being argumentative, i just don't understand the
advantages yet. perhaps if the total field count was
several hundred but i don't see my table having more than
50 fields. is this too many for one table? i realize
that's a subject question.

thanks again. sorry so long.
mas

 

[John Vinson]

it seems (for my db) the project report would always be
one row of data related to the project. what do i gain by
spliting up the tables?

Perhaps, in this case, nothing. If that's the case you can use an
(unusual but not unheard-of) one-table database; or you might want to
make your life simpler and just use an Excel spreadsheet.

 

[TC]

Also see: http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC