Here is the situation, I'm looking for advice on how to proceed.
I currently have two tables: tableA and tableB. I have a thrid table that
defines relationships between records in tableA and tableB. So the third
table, call it linkAB has the fields:
id (PK)
tableA_id (fk)
tableB_id (fk)
status (fk to a lookup table of statuses of the relationship).
As the status of the relationship between the tableA record and table B
record change I simply update the status code where tableA_id = whatever and
tableB_id = whatever. This works fine but I don't have a history of all the
statuses the relationship proceeded through. I need to add this ability.
So my thought is to simply insert a new record into the linkAB table for
each status change, and add a create_date field that will timestamp when the
relationship entered that status. This will allow me to report on the
history obviously. My question is this: I still will need to be able to
easily fetch the current status. Would it be better to add another field to
the linkAB table that would be a bit field "iscurrent". Then when I add a
new record to the table I update all the old ones to set iscurrent = 0. Or,
would it be easier, more normalized, to get the most current record based on
the timestamp? I'm leaning towards the latter, so I don't have to add the
field and do the update on the old records, but I'm not sure of the syntax
for doing this. My only thought is this, which seems real clunky:
select status
from linkAB
where tableA_id = #var# and
tableB_id = #othervar# and
create_date = (select max(create_date) from linkAB where tableA_id = #var#
and tableB_id = #othervar#)
The only reason this seems clunky to me is because of the number of queries
I need to get the current status in. And in lots of places it's in
sub-queries as well.
One final option I could do is leave all the queries and the linkAB tabe as
it is and just add a history table that stored muliple records for each A/B
relationship. This seems redundant though.
What would you do?
Thanks in advance for any advice.
- Sean
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sean Daniels
Manager, Engineering
Marketplace Technologies
[EMAIL PROTECTED]
http://www.dealstream.com
http://www.mergernetwork.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tel: 207.439.6030
cel: 978.764.0799
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
