Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Link table with datetime field Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 9:56 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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
Post #1363587
Posted Monday, September 24, 2012 10:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1363594
Posted Monday, September 24, 2012 10:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
Sean Lange (9/24/2012)
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.


Hi Sean,
Thanks for the response - yes, that's what I'm after. I know I won't be able to track previous updates, but that's not part of the spec nor important.
Guess I'd always call this type of design a link/junction table, as to me it's handling a m-m relationship, but then audit table is probably a closer definition to its purpose!
In terms of design, is there another way to accomplish this? Obviously I'm not going to add updateserver1,2,3... fields to the sourcefile table, but I can't see another way.

Cheers
Post #1363607
Posted Monday, September 24, 2012 10:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
Gazareth (9/24/2012)
Sean Lange (9/24/2012)
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.


Hi Sean,
Thanks for the response - yes, that's what I'm after. I know I won't be able to track previous updates, but that's not part of the spec nor important.
Guess I'd always call this type of design a link/junction table, as to me it's handling a m-m relationship, but then audit table is probably a closer definition to its purpose!
In terms of design, is there another way to accomplish this? Obviously I'm not going to add updateserver1,2,3... fields to the sourcefile table, but I can't see another way.

Cheers


OK that is what I thought. Certainly don't change your design to the denormalized version. Maybe think of it as a history table instead. For what you are tracking I don't see anything wrong with your schema. Maybe change the table name to ServerUpdateHistory so it is clearer what it means? I think you are just overthinking it. You got it right but confused yourself by thinking it was a junction table. Those are typically for things like a junction between Users and Permissions so you can find all Permissions for a given user.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1363614
Posted Monday, September 24, 2012 10:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
Sean Lange (9/24/2012)
I think you are just overthinking it.


Ha, almost certainly!

Thanks for your input Sean, just had a nagging thought at the back of my head that I can put to bed now.
Post #1363623
Posted Monday, September 24, 2012 10:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
Gazareth (9/24/2012)
Sean Lange (9/24/2012)
I think you are just overthinking it.


Ha, almost certainly!

Thanks for your input Sean, just had a nagging thought at the back of my head that I can put to bed now.


Sweet I can call it a day then before lunch. I made one person's day a little easier and he can sleep tonight.

Sometimes you just need another person to throw your ideas against. Glad I could be your backboard today.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1363628
Posted Monday, September 24, 2012 11:18 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:00 PM
Points: 8,289, Visits: 8,741
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?
....

Cheers

No, it isn't true, it's perfectly reasonable to add columns to a link table to describe attributes of the link (in this case, the attribute is the time when the link was created, I imagine). If you want to describe dating of links in a many-many relationship (which is a pretty common requirement) this is the right way to do it. Your DDL appears to generate exactly that, so if that's what you want there's no earthly reason not to do it that way.
O
f course there are some people around who say that a link table should have only its several foreign keys to identify the objects being linked, and a surrogate primary key - any attributes of teh link have to be consigned to another table which shares that primary key. But saying you have to do it that way is dognmatic nonsense, not relational theory.


Tom
Post #1363642
Posted Monday, September 24, 2012 11:20 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:00 PM
Points: 8,289, Visits: 8,741
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
Post #1363644
Posted Monday, September 24, 2012 11:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007

I don't understand what you are getting at, Sean.


Tom I was typing before I really understood the question completely. Maybe if I had used the word history instead of audit it would have been more clear. Either way within the next couple posts I was in total understanding and also said his ddl looks totally fine. I took the bumpy road to the same conclusion that you have, no need to change anything.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1363646
Posted Monday, September 24, 2012 11:32 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:00 PM
Points: 8,289, Visits: 8,741
Sean Lange (9/24/2012)

I don't understand what you are getting at, Sean.


Tom I was typing before I really understood the question completely. Maybe if I had used the word history instead of audit it would have been more clear. Either way within the next couple posts I was in total understanding and also said his ddl looks totally fine. I took the bumpy road to the same conclusion that you have, no need to change anything.

Yes, I saw that after I'd posted - there was a bit of conversation between you and the OP while I was typing - I should have spotted that an not posted something what was unneeded.


Tom
Post #1363652
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse