Database designing woes

[Guest]

Hi all,
I am designing a simple database and can't decide or figure out how exactly
to design a certain aspect. I have started it 2 different ways so far.

The database is mainly a history tracking and simple info viewing database.
I have a bunch of physical sites that each have a monitor (0 to M actually)
and each monitor has sensors to gather data (level, velocity, etc). I have a
table for Sites, Monitors, and Sensors as well as tables like SiteMonitorLog
and MonitorConfigurationLog to track the history of a site or monitor.

Now I designed the sites table to have an ID, Location, Name. Monitors has
ID, Serial#, Type. SitesMonitorLog to have a logID, fkSiteID,
fkMonitorSerial#, Action Done, Date Performed. However this design makes for
a real pain to query the log to see which monitors are free to be connected
at a site, or which monitors have connections at which sites. (Query the log
table to group monitors, find the latest data, see which are "removed" action
done. Similarly for which are connected with the "Installed" Action Done.
It was 3 nested queries for me).

Should I just do a simple cheat or hack to place a foreign key for SiteID in
the Monitors table and set it to the sites ID when it is connected and
something like -2 when it's not? This makes for a much easier query
designing process but I'm not sure if that is the way to do it. Of course I
would still have the log table and enter the correct data into it when I
add/rem a monitor to a site.

Sorry for the lengthy description and thanks in advance to all repliers!!!

 

[Guest]

Jebuz,

I'm not sure I follow you entirely, but it seems that some Actions taken
will change the status of the monitor on which it's taken. I suggest you add
a Status field to the Monitors table, and set it with an Update query in code
in the AfterUpdate and AfterInsert events of the log form. Then your query
becomes simple.

Hope that helps.
Sprinks

 

[Guest]

Thanks Sprinks,

I did however get my query to work but it looks horrible and I'm sure is WAY
less efficient than just adding a simple field like the status field you
suggested and querying off that. I think I will add as a foreign key to the
Monitors table a fkSiteID and another foreign key fkMonitorID to the Sensors
table. That way I can easily query which sensors are attached to what
monitors and which monitors are located at which sites. It replicates some
data but saves lots of headaches and is I am guessing more efficient.

Thanks again. On to my next problem... haha