Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2004-03-02 : 20:54:32
|
| I have a table called "TBLGROUPS_USERS" it stores all the users in a certain group. I am not sure which way to design it here. Either 2 columns or 3.groupID [int],userID [int]VScounterID [int](IDENTITY), groupID [int],userID [int]Are there any pros or cons in doing it one way or the other? Thanks again for the helpmike123 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-03-02 : 20:59:22
|
| What's the key Mike? Seems it should be a composite of GroupID and UserID.. If that is the case then counterID will be redundant...DavidM"SQL-3 is an abomination.." |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-03-02 : 21:11:10
|
| I've always had a habit of designing tables with an INT column as an identity, and I've done it on my current application in the same scenario. But as I'm learning more I realized it does sound redundant.Your correct, the key is a composite of groupID and userID. I guess I should make a composite key of both (havent done this in a long time eek).How would you approach this insert? Now that its a composite key I have to make sure I dont do a primary key violation, correct? I should also return something to let the web app know if it was successful or not.What do you think of this?Thanks again!!CREATE PROCEDURE [insert_tblGroup_Users] ( @UserID [int], @groupID [int], @Successful [tinyint] OUTPUT )AS SET NOCOUNT ONIF Exists (SELECT userID FROM tblGroup_Users WHERE userID = @UserID AND groupID = @groupID)BEGIN SELECT @Successful = 0 ReturnEND BEGIN INSERT INTO [dbo].[tblGroup_Users] ([UserID], [groupID]) VALUES (@UserID, @groupID) SELECT @Successful = 1 ENDGO |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-03-02 : 21:55:04
|
| If you don't have a PK (or Unique Constraint) on the composite columns you will get duplicates. Far worse than a key violation....I mostly don't do any error checking for a key violation, that is the databases job... I let the error bubble to the executing language...DavidM"SQL-3 is an abomination.." |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-03-04 : 15:18:10
|
Have to agree with David on this. The counterID would allow duplicates. This (juncture tables) is where I first started to break my dependency on IDENTITY fields, (and I feel much better now, thank you for asking ).Why the concern over PK violations in your juncture table? I'd think that the maintenance of this table would be limited in scope, and whatever piece of your application allows a user to be added to a group should already know whether the user belongs to that group or not and thus disallow the duplicity. Or, as David mentions, just handle the error in the application once it occurs.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
nfsoft
Starting Member
36 Posts |
Posted - 2004-03-04 : 19:32:23
|
| I have a table just like that... I am using UG(ID_UG, ID_USER, ID_GROUP).The reason to this is becouse I am working in a dinamic enviroment (as every thing), an in future it may be needed to add a history on the table like UG(ID_UG, ID_USER, ID_GROUP, ID_STAT, CHANGE_DATE)... If this will come I will have no problem.Any System(Bisness) roule I will deal with a good trigger, check or/and constrain but the data will never depend on the system.So the main thing is: if you are not sure use "counterID [int](IDENTITY), groupID [int],userID [int]"If you know your system will not grow, flip the coin.I terms of perform. You will see less CPU in a (PK,PK) but you will get more I/O, and in (PK, FK, FK) you will have more CPU time and less I/O. Performance is more less the same.Nuno Ferreira |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-03-04 : 19:44:06
|
| Nuno,Auditing or history is usually best achieved by creating a new table and adding temporal columns.A series of triggers on the core table track the changes in the history table. There is code on the net that automatically builds the tables and triggers....The benefit is you have your current valid data only in the table in question..As a DBA, IMHO, your priority is data consistency and integrity....What does this mean???"Any System(Bisness) roule I will deal with a good trigger, check or/and constrain but the data will never depend on the system." DavidM"A Human Clone is a result of not implementing a Key Constraint." |
|
|
|
|
|
|
|
|
simple question on table design