Adding a table, if needed or something else.....

[Allen Jaffee]

Hello,
Hope I'm posting to correct NG.

I have a <tblProjectorData> with associated data. Another
<tblProjectorService> with servicing history. I would like
to have another tbl with location history which is based on
the first tbl data (as it contains location information),
and use a form that would be based on this tbl (i.e., I need
to be able to update or change the location history,
basically tracking where unit has been, much like I am doing
with the <tblProjectorService>).

Would this be a many-to-many relationship, thus a third tbl,
or am looking in the wrong area?
Any nfo to help the design of the tbl would be helpful.
Perhaps it is not even a new tbl that I need.

Thanks for the help.
Al

 

[Tim Ferguson]

I have a <tblProjectorData> with associated data. Another
<tblProjectorService> with servicing history.

Okay, sounds fine...

I would like
to have another tbl with location history which is based on
the first tbl data (as it contains location information),

Surely Location information should be in a Locations table?

and use a form that would be based on this tbl (i.e., I need
to be able to update or change the location history,
basically tracking where unit has been, much like I am doing
with the <tblProjectorService>).

Yes, it sounds like you need another table called

IsToBeFoundIn
(*ProjectorID, *Location, DatePlacedThere, DateRetrieved)

with the PK made up of the first two fields, which are also pointers to the
PKs in the Projectors and Locations tables.

Hope that helps


Tim F

 

[Allen Jaffee]

Well, I guess I just wasn't sure. Was thinking along the
lines of what you described. With your description, is that
a many-to-many relationshp? Or would the relationshps use
enforce referential int?
I placed data in a couple smpl tbl's to play with scenarios
as I understand that good tbl design is basis for problem
free results. I just didn't know if this would be good and
wanted to hear some various input.
Thx.
Al

 

[Tim Ferguson]

Well, I guess I just wasn't sure. Was thinking along the
lines of what you described. With your description, is that
a many-to-many relationshp?

Yes: remember that relationships can have attributes too, as in this
instance there are start and finish dates.

Or would the relationshps use enforce referential int?

Without referential integrity there _is_ no relationship. Relationships
defined in a database are control mechanisms (constraints) that say, "this
field can only contain values from that field in the other table". This
works both ways, so the same rule says "you can't delete a record from the
other table if one of these fields points at it." That's the whole point.

I understand that good tbl design is basis for problem
free results.

Absolutely.

B Wishes


Tim F

 

[Allen Jaffee]

Tim,
In re: to tbl design, what about 2 seperate tbl's
<tblLocationHist> & <tblLocation> ?
In my orig ?? I was trying not to have duplicate data, but
since needing to track, would the above 2 tbls be redundant?

Thx again, apperciated.
Al

 

[Tim Ferguson]

In re: to tbl design, what about 2 seperate tbl's
<tblLocationHist> & <tblLocation> ?
In my orig ?? I was trying not to have duplicate data, but
since needing to track, would the above 2 tbls be redundant?

Stuff redundancy: think entities. There are a number of "things" that you
need to model -- for example, we already know about Projectors and
Locations and Placements. You need one table for each entity.

The whole thing about "data redundancy" was invented to drag people over
from historical dinosaurs like network and heirarchical databases to the
relational model. At that time, memory and disk space were sparse and
expensive and it was a good argument to convince them that R theory could
help them.

Now that both are cheap, it's time to embrace the real idea: that R theory
is about protection and integrity and control. You shell information out
into a new table because it prevents data contradictions. It usually does
not save space: a new table and its indexes and the processing required to
check each update and so on are not things that you would expect to improve
efficiency.

But then again, what price do you put on registering a patient with a non-
existent doctor? Or having two different addresses for the same location:
where do you look for your projector then?

Sorry, that turned into a bit of a lecture... <g>

All the best


Tim F