Normalizing a table

D

Drew

When normalizing a table. I will have several different sites each with the
same layout information. Each site will have its own simple calculations (+ -
and Totalize) to do and store, daily, monthly, yearly.

I think by reading the different posts that I can set up a Combo box later
allowing a choice of site, but because the entered data will be different at
each site should each site need it’s own table.

Also am I correct in saying the Primary Key would be the individual site?
 
K

KARL DEWEY

Sounds like you need two tables.
The first will identify the site and have a primary key. Have fields for
information on the site like location, description, contact, owner, etc.
The second table will be used for your data collection and have a foreign
key to match the main site table primary key. Have fields for the data
collection - datetime, measurements, remarks, etc.
 
D

Drew

Hi Karl, thanks for the help, does this look better/worse?

I have taken the information I require and laid it out in a flat format. I
then I made tables combining the groups. Might this look more like proper
normalizing? Please keep in mind that each “Well Site†will need info from
the other tables below it. I will delete the spaces before I put it into
access.

Filter Storage
tbl Date Time
tbl filters Added
tbl filters Total
tbl filters to well sites

Well sites
tbl A8
tbl A10
tbl A12
tbl A17
tbl B6

Filter Skids
tbl skid 1
tbl skid 2
tbl skid 3

Skid Thru Put
tbl totalizer start
tbl totalizer finish

Skid Totalizer
tbl totalizer 1
tbl totalizer 2
tbl totalizer 3

Filter Change
tbl filter change 113
tbl filter change 66

Filters on Site
tbl filters added
tbl total filters on site
 
J

John Vinson

Hi Karl, thanks for the help, does this look better/worse?

I have taken the information I require and laid it out in a flat format. I
then I made tables combining the groups. Might this look more like proper
normalizing? Please keep in mind that each “Well Site” will need info from
the other tables below it. I will delete the spaces before I put it into
access.

Filter Storage
tbl Date Time
tbl filters Added
tbl filters Total
tbl filters to well sites
Click to expand...

What are the "tbl" prefixes here? Do you really mean to have separate
tables for "filters added", "filters total", "well site A8" and so on?
If so - you're WAY off track!

John W. Vinson[MVP]
 
J

John Vinson

When normalizing a table. I will have several different sites each with the
same layout information. Each site will have its own simple calculations (+ -
and Totalize) to do and store, daily, monthly, yearly.

I think by reading the different posts that I can set up a Combo box later
allowing a choice of site, but because the entered data will be different at
each site should each site need it’s own table.

Also am I correct in saying the Primary Key would be the individual site?
Click to expand...

Will the *shape* of the data - that is, the number of fields or the
nature of the data in the fields - be different from well to well? If
not, then you should have just ONE table of well data, with a
SiteNumber field. I'd expect you would have a table of Sites, with
SiteNumber as the primary key; this would be related one-to-many to a
table of Measurements, with the SiteNumber as a foreign key.

Calculated values, and daily, monthly, and yearly totals, should
generally NOT be stored in *any* table. They should instead be
generated on the fly using totals queries. The only exception would be
a DEMONSTRATED BY EXPERIMENT, not assumed, need to do so to overcome
excessively slow queries.

John W. Vinson[MVP]
 
D

Drew

Good Morning John,

Filter Storage would be the table name. tbl Date Time, tbl filters added,
etc would be what is inside the table.
Well Sites is the table name. tbl A8, tbl A10, ect again would be what is
inside the table. I was reading this was the correct way?

The information I require will be the same at each well site (A8, A10) etc.
The only difference is the totalizer start, finish and the the filters
consumed.

I am thinking I should lay all this out in a word format explaining what I
am trying to do.

Please be patient in a delay of my response time as I am leaving work today
and I will not get home until Friday. While I am traveling I will write my
story. Maybe then we can look at a starting point on how to lay every thing
out. I have looked at a lot of posts and I can see a lot of newbe's have
dificulty with this. Maybe this will be helpfull to others also.
 
J

John Vinson

Good Morning John,

Filter Storage would be the table name. tbl Date Time, tbl filters added,
etc would be what is inside the table.
Well Sites is the table name. tbl A8, tbl A10, ect again would be what is
inside the table. I was reading this was the correct way?
Click to expand...

A couple of suggestions: I'd be inclined to use the prefix tbl (if at
all) only for Table names, not for the names of fields within the
table.

Much more importantly: if you have different fields for each well site
- YOUR DESIGN IS WRONG. Well sites ARE DATA, not attributes of an
entity.
The information I require will be the same at each well site (A8, A10) etc.
The only difference is the totalizer start, finish and the the filters
consumed.
Click to expand...

Then you should have two (at least) tables - a table of WellSites,
related one-to-many to a table with start, finish, and filters
consumed fields.
I am thinking I should lay all this out in a word format explaining what I
am trying to do.

Please be patient in a delay of my response time as I am leaving work today
and I will not get home until Friday. While I am traveling I will write my
story. Maybe then we can look at a starting point on how to lay every thing
out. I have looked at a lot of posts and I can see a lot of newbe's have
dificulty with this. Maybe this will be helpfull to others also.
Click to expand...

I'll look forward to the post on Friday, I think we can come up with a
normalized design here!

John W. Vinson[MVP]
 
D

Drew

Hi John,

Hope everyone had a great New Years calibration and all the best in 2006.


I will start at the beginning.

When we order filters we will receive them in 50,000 lots and store them for
use as needed. That area can hold up to 100,000. I am thinking that the only
item to be in this tale is the stored number as adding to it would be in
queries.

The date and time (24hour). As for the filter storage area the only thing
that is important is the date. On the different well sites the date and time
is important. This is so we can track how many barrels of water we are
injecting and the duration.

If I understand your earlier post I have many entries that are not related
to tables but more to queries.

From the Lay down yard we move the filters to the different well sites A-8,
A-10, A-12, A-17 & B-6. There will be more added later. This could be set as
a table. Also would each well site be a Primary Key and a Foreign Key to the
totalizer table?

At this point I am just not getting it on what should be in this table. I
will say what is there and recordable.
We have a totalizer at each site and will record up 7 digits. So when a
filter pot is placed on line I would like to record the Start number and when
the filters are used up I would like to record the Finish number. These are
the only true recordable items at a well site that could be set up as a table?
Filters at well site number I am not sure if this should be in a table or a
queries?

I will stop here for now to be sure I am going in the right direction. If
this looks ok I would like to continue on to the next step.

--

Thanks & Regards,
Drew
 
Top