Sean Lange (9/24/2012)
Gazareth (9/24/2012)
Hello,I seem to recall from my database design classes a while back, being told that a link table with just a datetime field in wasn't really a link table.
I've just designed exactly that, but can't see another way?
Is this true, and what would the alternative be?
create table sourcefile (sourcefileid int primary key, name varchar(500));create table updateserver (updateserverid int primary key, name varchar(500));
create table sourcefiles_updateserver (sourcefileid int, updateserverid int, updatetime datetime,
primary key (sourcefileid, updateserverid),
foreign key (sourcefileid) references sourcefile (sourcefileid),
foreign key (updateserverid) references updateserver (updateserverid));
Cheers
That certainly looks a bit off. Typically this type of junction table is used to handle a many to many relationship. Not sure what you are trying to track here but I don't think this is what you want. Are you trying to audit when a given sourcefile was used to update a specific server? The way you have this coded you can only ever update a specific server with a given sourcefile. If that is what you are after then this is NOT a junction table it is more like an audit table.
I don't understand what you are getting at, Sean. This looks like a perfectly ordinary link table, used to record some history: each row could be saying when a particular sourcefile became available from a particular server, or recording the time for some other event which happens at most once for each sourcefile-updateserver pair - that's an absolutely bog-standard many to many relationship (which files are available from which servers) tagged with the time at which a particular individual pair came to be related. The only imaginable reason it might be called an audit table is that it records those times, but to me an audit table records whodunnit as well as when. Where you got "The way you have this coded you can only ever update a specific server with a given sourcefile" from is completely beyond me - there is no restriction here to a many to one (or one to one if that's what you meant) relationship. You can see that be creating the tables with the constraints as shown and then by populating them with values which couldn't live in a many to one world, perhaps values like this:
insert updateserver values
(0,'neoni'),(1,'aon'),(2,'dhà')
insert sourcefile values
(10000,'ailm.nfo'),(10001,'beith.nfo'),(10002,'coll.nfo')
insert sourcefiles_updateserver values
(10000,0,'20120101'),(10001,0,'20120401'),(10002,0,'20120903'),
(10000,1,'20120704'),(10001,1,'20120924'),(10000,2,'20120921')
select sourcefileid, updateserverid, updatetime
from sourcefiles_updateserver
drop table sourcefiles_updateserver
drop table sourcefile,updateserver
Tom