September 24, 2012 at 9:56 am
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
September 24, 2012 at 10:11 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2012 at 10:26 am
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
September 24, 2012 at 10:31 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2012 at 10:38 am
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. 😀
September 24, 2012 at 10:46 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2012 at 11:18 am
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
September 24, 2012 at 11:20 am
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
September 24, 2012 at 11:25 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2012 at 11:32 am
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
September 24, 2012 at 11:35 am
L' Eomot Inversé (9/24/2012)
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.
Nah because if it had not been cleared up I would have asked the same thing. As I read my own post I kind of wonder where my brain was going because it had clearly wandered far off track. Need to engage the brain before the keyboard sometimes.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 25, 2012 at 3:45 am
Thanks guys. In this case, the 'whodunnit' is the updateserver, so no need for another field.
The updatetime is the date/time the sourcefile was last processed by the updateserver, and so can be updated after initial creation.
So basically it's a history table masquerading in the format of a junction table.
The original question was something half-remember from a db design class years ago - specifically where there's only the two FK's/compound PK and a datetime field. This is the first time since then that I've created a table with this specific format so not really a worry!
Daftly enough, if there had been additional attributes within the table then I wouldn't have had any doubts.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply