Database Design--which tables to use?
rache...@my-deja.com
Working on a database for a local homebuilder;
there's a lot of information being tracked on
each specific job (aka House) in progress, and
I've got most of it organized but still one
remaining question I'd like some advice on.
I'd like to track information about the sub-
contractors working on various phases of
development of each of the houses--stuff like
their ID, a grade, notes, etc. Question is, there
would be so many fields I am not sure how to
organize a table strucure! Below is how it would
look all in one table (hope it makes sense!):
Table: Job_Phases
-----------------
Lot (Primary Key)
Layout- Cement Finisher ID
Layout- Cement Finisher Grade
Layout- Dirt Supplier ID
Layout- Dirt Supplier Grade
Framing- Framer ID
Framing- Framer Grade
PreFinal- Cabinet Guy ID
PreFinal- Cabinet Guy Grade
PreFinal- Flooring Guy ID
PreFInal- Flooring Guy ID
So for each lot, there are 10 phases of building
(Layout, Framing, etc.) and for each phase, there
is anywhere from 1 to 5 sub-contractors. I was
wondering if it makes more sense to make 10
tables, one for each phase, but then the
relationships get messier (each of the ID's is
linked to table Sub_Information and the grades
linked to table Grade_Information). And since all
info is still related to the primary key (Lot) I
had trouble coming up with other options.
Any advice would be most appreciated!!
Thanks,
Rachel
Sent via Deja.com
http://www.deja.com/
peter walker
Lot Table
Lot, Layout, ContractorID
The Grade is a property of the supplier?. Use a type field for cabinet or
flooring and a phase id (assuming the cabinet guy doesn't do cement
finishing as well).
Contractor table
ContractorID, Type, Phase, Notes
Job Type table
JobTypeID, Description
Phase Table
PhaseID, Phase Description
Enter all the possible phases. Lot phase form.
Enter all the possible job types. Job type form.
Enter you contractors, combos for the above types. Contractor form.
Then you can enter the details for the lot.
CrossTab and normal queries will be able to present the data as required.
Assuming you will use this for many Lots, and perhaps use the same group of
contractors, it is only the initial support table info that takes a while to
enter. After that every new lot should go like smoke.
--
peter walker
LZ129: - Don't paint your airship with rocket fuel.
ICQ 85542363
Compuserve 106244,2032
various things available from
http://www.users.bigpond.com/papwalker/#Downloads
.
<rache...@my-deja.com> wrote in message
news:93n5um$dug$1...@nnrp1.deja.com...
rache...@my-deja.com
Thanks for your reply. What you said sounds good except for one thing:
each contractor could be responsible for more than one phase (i.e.
Jim's Painting could be doing Interior:Paint and Exterior:Paint for one
lot or on different lots.
Also, I was a little unsure as to how the Job Type Table fit in with
your overall plan...could you clarify?
Thanks so much,
Rachel
In article <LhF76.1962$65.1...@newsfeeds.bigpond.com>,
Albert Marshall
AAAAAAAAAAAAARGH!!!!!!!!!!!!!!!!!!!!!!!
>one for each phase, but then the
>relationships get messier (each of the ID's is
>linked to table Sub_Information and the grades
>linked to table Grade_Information). And since all
>info is still related to the primary key (Lot) I
>had trouble coming up with other options.
>
One table (plus lookup tables to feed combos).
tblPhaseContractor
RecordID (Autonumber PK)
LotID (Fk to tblLot)
PhaseID (FK to tblLkpPhase)
ContractorID(FK to tblContractor)
Put in a composite unique index across LotID, PhaseID and ContractorID,
to avoid putting the same person in twice for the same phase.
You can have this in a subform with the main form based on tblLot.
The contractor type and grade is presumably an attribute of Contractor,
but if you have multi-skilled people you could add
ContractorTypeID(FK to tblLkpContractorType)
GradeID (FK to tblLkpGrade)
(You'll need to include ContractorTypeID in your unique index)
Using this approach you can query for all the contractors by Lot, Phase,
Type, Grade or any combination of those.
By the way, what do you mean by "Grade" -- how good they are?
--
Albert Marshall
peter walker
Jobs??
The job types would be as listed and when setting up a supplier, you select
it from the combo.
Cement Finisher
Dirt Supplier
Framer
Cabinet
Flooring
The reason I went this way was a painter generally does not do electrical.
So painting the is main method of Jims Painting. So by assigning a
contractor and phase to a Lot you could pull out
Lot Number Phase, Job, Contractor
1124 PreFinal Painting Jims Concrete
But if Jim is up for two methods we are sunk trying to use him to indirectly
get the Job per Lot.
If you approach from another angle where the job becomes a property of the
LOT then you must have a Main table like...
Lot, Phase, ContractorID, JobID
But it is now possible to have Jim doing electrical if the operator botches
the input.
Now to get slightly more complex, if another table was set up ...
ContractorSkillSet
contractorID - jobId
Jim's Painting Interior:Paint
Jim's Painting Exterior:Paint
In the contractor form, a subform is used to enter appropiate skills.
Then when the operator adds records to the main table you could then check
and stop someone assigning him to electrical.
The added advantage of all this is that a list of contractors / skill can be
produced. Whether they are on jobs at the moment etc. New jobs can be added
as time goes by.
You might want a completed or status flag for each record in the main table.
All this may sound complex, but as time goes by you won't regret taking the
extra time.
--
peter walker
LZ129: - Don't paint your airship with rocket fuel.
ICQ 85542363
Compuserve 106244,2032
various things available from
http://www.users.bigpond.com/papwalker/#Downloads
.
<rache...@my-deja.com> wrote in message
news:93ncig$kdu$1...@nnrp1.deja.com...
rache...@my-deja.com
Thanks for your replies to both my postings--your advice is really
helpful. As someone who just kind of "fell" into Access, it's hard to
take into account basic database design principles when you've got a
deadline!
And yes, by "Grade," we mean how well the contractor did on a certain
job (So that we know whether or not to use them again!).
--Rachel
In article <Jr4r$vBT5z...@execfrog.demon.co.uk>,
rache...@my-deja.com
I took your advice and implemented a new table structure, but I've got
a question for you (or anyone else!). First, here are my tables:
tbl_Lot_Phase tbl_Subs tbl_Lots tbl_Grades tbl_Phases
-------------------------------------------------------------------
RecordID SubID LotID GradeID PhaseID
LotID Company Plan Description Description
PhaseID Phone Status
SubID
GradeID
Now, with my last table scheme, I was able to have a form with one
record for each of 120 lots. On the form were 10 tabs, one representing
each phase. The info under the tabs came from the Grade, Phase, Lot &
Sub tables.
With this new structure (above), I've made a form based on tbl_Lot (so
there are 120 records) but am now confused as to how to get the other
info. on the form. You mentioned using a subform, but since in
tbl_Lot_Phases there is more than one entry for each lot, how do I go
about doing that so the subform contains only the 10 records associated
with the one lot displayed in the main form?
Thanks for all your help so far!
Albert Marshall
>Albert--
>
>Thanks for your replies to both my postings--your advice is really
>helpful. As someone who just kind of "fell" into Access, it's hard to
>take into account basic database design principles when you've got a
>deadline!
I know it's hard to believe, but that's when it's most important! Get
the structure right and the rest is much easier (= quicker).
After all, it's much quicker building a house if you don't spend time
drawing the plans first!
If you are going to do any more development you really should read a
book on data design. I recommend either Steven Roman's "Access Database
Design and Programming (O'Reilly) or Rebecca Riordan's "Designing
Relational Database Systems" (Microsoft Press).
--
Albert Marshall
Albert Marshall
>Hi ALbert--
>
>I took your advice and implemented a new table structure, but I've got
>a question for you (or anyone else!). First, here are my tables:
>
>
>tbl_Lot_Phase tbl_Subs tbl_Lots tbl_Grades tbl_Phases
>-------------------------------------------------------------------
>RecordID SubID LotID GradeID PhaseID
>LotID Company Plan Description Description
>PhaseID Phone Status
>SubID
>GradeID
>
>
>Now, with my last table scheme, I was able to have a form with one
>record for each of 120 lots. On the form were 10 tabs, one representing
>each phase. The info under the tabs came from the Grade, Phase, Lot &
>Sub tables.
>
>With this new structure (above), I've made a form based on tbl_Lot (so
>there are 120 records) but am now confused as to how to get the other
>info. on the form. You mentioned using a subform, but since in
>tbl_Lot_Phases there is more than one entry for each lot, how do I go
>about doing that so the subform contains only the 10 records associated
>with the one lot displayed in the main form?
>
have set relationships between the tables.
In the database window select tbl_Lots and use Insert...Form and pick
the Form Wizard. Select all the fields in tbl_Lots, then change (using
the combo) to tbl_Lot_Phase and again select all the fields.
Work through the rest of the wizard and you should end up with a basic
combination of forms. You can then format it to suit yourself.
You'll need to put in combo boxes to select from the various lookup
tables, but you should soon get the hang of it.
If you haven't got an Access book yet I suggest you buy "Access 2000 at
a Glance" from Microsoft Press. It covers all you need to know for using
the basic features of Access (even a brief intro to VBA) and it's clear,
well illustrated and quite cheap by Access standards.
--
Albert Marshall
George Vaughn, CPA, MBA
you can relate job phases to multiple sites, multiple contractors to job
phases. Each site has a unique ID (autonumber). So does each contractor,
and each job phase. Change many-to-many into one or more one-to-many
relationships. Then run your queries against these joined tables.