Access 2002 Database Newbie

[Boomer]

I am trying to develop a access database version 2002
from scratch and I am a novice programmer and need much
direction. I have been researching and studying about
relational database design and normalization I am
including the 9 tables and there fields that I have
already developed. I have and I am not sure what to name
the tables and I am thinking of making the incident #
field the pimary key?? Can I put that in all the tables?
I am determined to develop this and do it well. We are a
Refuse Disposal site , is there any Disposal Management
Database examples out there... If not can I get some
free advice and help in designing this database . Are my
tables looking okay? Can I use the( Incident # )as the
primary key for all the tables to connect them ? In one
of the tables I want to put digital pictures of the
dump sites can I develop a table for this to get them to
show up on the form..or do I need another database??
There are about a 100 pic's.. in all. I have to scan
them in using the .bmp ext. is this okay???. My OS is
Win Xp Home Editon and Office Xp Pro 2002 I only have 128
Mb memory..Do I need more? Here are the tables with the
fields: I have gathered the following information from
the 3 forms that the staff fills out by hand and then the
secretary takes 3months worth and then types them out to
look better. I am confused on how to design the table
logic and make it relational

Customer table
1. Location/Name
2. Incident #
3. Directions from the court house
4. Latitude and Longitude
5. Dump Size = (ft) Width x (ft)Length x (ft)
Depth = (ft)to the 3rd power volume
6. Single Axle Dump trucks
7. A water Well within the dumpsite (this is a yes
no box)
8. Date Dumpsite Identified
9. Date to Clean
10. List any local schools or historic sites
11. Responsible Party (yes/no)
12. Enforcement actions Underway (Yes/No)
13. County
14. Nearest road
Contents table (All Yes/No Boxes)
1. Household
2. CD/D Building Material
3. CD/D Concrete
4. Brush/Landscape
5. Appliances
6. Tires
7. Hazardous Material
8. Other / List

Accessibility Table Y/N Boxes

1. Paved
2. Gravel/dirt
3. steep slope
4. trees surrounding site
5. water surrounding site
6. other/ List

Type of setting Table Y/N Boxes

1. Public Property
2. flood plain/floodway
3. open field
4. sink hole
5. cliff
6. hillside
7. woods
8. creek/river
9. old quarry pit
10. roadside
11. other/list

(Digital Photos Table)

Equipment Cost Table
1. Equipment Type
2. Max Hourly Rate
3. actual hourly rate
4. hours used
5. total

Disposal Fee table

1. Facility Name and location
2. Tons estimated
3. Per ton cost
4. total

Labor Cost table

1. Employee Title
2. estimated work hours
3. hourly wage
4. total

Miscellaneous Supplies Table

1. Specific Item
2. quantity
3. per unit cost
4. total

Thanks John

 

[Tim Ferguson]

We are a
Refuse Disposal site , is there any Disposal Management
Database examples out there...

I am certainly not aware of it, but then again I haven't looked! Have you
searched on Winfiles, Google and so on? What are your colleages/
competitors/ contacts using?

If not can I get some
free advice and help in designing this database.

Well, that's roughly what we are here for.

Are my tables looking okay?

It's a bit hard to say from the description you have given here: at a guess
I think the kind of entities you should be looking at would be


Dumpsites(SiteNum, Name, Directions, Size, Access etc etc)

Incidents(sorry, I really don't know what an incident is...)

ContentTypes(CTypeCode, Description)

DumpsiteAllows(SiteNumber, CTypeCode)

AccessTypes(ATypeCode, Description)

DumpHasAccess(SiteNumber, ATypeCode)

SettingTypes(STypeCode, Description)

DumpIsIn(SiteNumber, STypeCode)

Equipment(ItemID, etc etc -- what does equipment belong to??)

DisposalFeeRates( ditto )

Employees()
HoursWorkedOn(SiteNumber, EmpNum, NumOfHours, Rate _
I'm really guessing now...)

Supplies(SiteNumber, DeliveryDate, Item, Quantity, Invoiced, etc)


and so on and so on. The tables you suggested smack to me more of functions
than of "things". I note you start with Customers, which normally refers to
some kind of person, but there were no personal details like Name or
Address -- should this be in there too?

Hope that helps a bit


Tim F

 

[Jeff Boyce]

John/Boomer

Where to start?!

First, the only reason you'd put "Incident#" as a primary key in every table
would be if all your tables were related "one-to-one" to each other -- and
from a quick glance, they are not. When I build a relational database, I
first think about the "things" and their "attributes", then about how the
things are "related". I'd suggest stepping back from your current table
design and reconsidering these, using that old standard design toolset, the
"paper and pencil"!

In your description, you mention DisposalFee and TypeOfSetting tables.
While I could see that you might want to relate these to some of your other
tables, surely they aren't related to every "thing" (i.e., TypeOfSetting
isn't related to DisposalFee, is it?)? And it seems to me that a given
Customer/Site might have multiple applicable TypeOfSettings (this would be a
one-to-many relationship, if so).

A word of caution -- the tablesdbdesign newsgroup has a lot of posts about
using photographs. You DON'T want to put the photos in your table(s) -- it
causes major bloat of your database. Instead, put them in a file folder and
use a field in a table to "point" to the location/filename. When your form
loads the record, use the pointer to load just that image onto your form.
Again, if there are multiple photos per site, this is a one-to-many
relationship -- you need a table structure to reflect this relationship.

You've embedded multiple (potentially) values in a single field (local
schools/historic sites nearby). This is generally not a good idea, as
fields in a relational database need to have a single value.

Your Contents table also appears to have a one-to-many relationship, but, by
having multiple checkboxes, you've made it very difficult to write queries
that return WHICH contents a specific site holds. A more "relational"
design would be to use the Content table to list potential Contents, and
create a relation/join/junction table to hold one row for each combination
of Customer & Content.

There's more, but how 'bout if you use this to get started? Post back with
further questions as you build this system.

 

[Guest]

OKay

>-----Original Message-----

>First, the only reason you'd put "Incident#" as a

primary key in every table

>would be if all your tables were related "one-to-one" to

each other -- and

>from a quick glance, they are not. When I build a

relational database, I

>first think about the "things" and their "attributes",

then about how the

>things are "related". I'd suggest stepping back from

your current table

>design and reconsidering these, using that old standard

design toolset, the

>"paper and pencil"!
>In your description, you mention DisposalFee and

TypeOfSetting tables.

>While I could see that you might want to relate these to

some of your other

>tables, surely they aren't related to every "thing"

(i.e., TypeOfSetting

>isn't related to DisposalFee, is it?)? And it seems to

me that a given

>Customer/Site might have multiple applicable

TypeOfSettings (this would be a

>one-to-many relationship, if so).
>A word of caution -- the tablesdbdesign newsgroup has a

lot of posts about

>using photographs. You DON'T want to put the photos in

your table(s) -- it

>causes major bloat of your database. Instead, put them

in a file folder and

>use a field in a table to "point" to the

location/filename. When your form

>loads the record, use the pointer to load just that

image onto your form.

>Again, if there are multiple photos per site, this is a one-to-many
>relationship -- you need a table structure to reflect

this relationship.

>

>You've embedded multiple (potentially) values in a

single field (local

>schools/historic sites nearby). This is generally not a

good idea, as

>fields in a relational database need to have a single value.

>Your Contents table also appears to have a one-to-many

relationship, but, by

>having multiple checkboxes, you've made it very

difficult to write queries

>that return WHICH contents a specific site holds. A

more "relational"

>design would be to use the Content table to list

potential Contents, and

>create a relation/join/junction table to hold one row

for each combination

>of Customer & Content.
>There's more, but how 'bout if you use this to get

started? Post back with

>further questions as you build this system.

>Jeff Boyce

>I am trying to apply naming conventions and organize

this I am having trouble with fields like dump size it
has that formula in it how do i do this?? and you
explained how to do the list any schools or historic
sites..its just a question on the form depending on where
the employee is at surveying the job so what did you
mean??....Here is how it works the employee takes the
form with him to a site that has junk and trash on it he
then surveys it and fills in the appropiate information
which is all the fields i have listed in the different
tables.does this make sense... so this is where i am
overwhelmed as to how to get it together its only coming
from 3 forms and we want this in the computer ....so we
can enter it in and then print out reports. like the one
that we use....also you mentioned the photo table and to
make a seperate folder and then connect it to a field in
a table and point it to the location and filename ...do i
do this by a ole object??? and on the contents and
accessibility and type of setting tables you said make
them one to many with the relation join junction to hold
one row for each record...???? where is this how to do???
I am sorry to be so elementary it will click with me
soon...i just have to keep plowing away at it...THANKS SO
MUCH for you help...can you give me some visual design as
to what you would do with what i have given you....