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 - 2008-06-09 : 14:09:24
|
Hi,I currently have an internal message system, and I want to modify the db design so users can create their own custom folders.Currently I have just this table in use, with the bolded column, the one I want to add. With this design, I am thinking of defaulting each "folderID" in this table to a value of 0, which will denote the standard inbox folder. I think this is better because I don't think its necessary or beneficial to have each user have their own row in this table just for their standard inbox.CREATE TABLE [dbo].[tblMessage]( [MessageID] [int] IDENTITY(1,1) NOT NULL, [MessageFrom] [int] NOT NULL, [MessageTo] [int] NOT NULL, [Message] [varchar](1500) NULL, [prevMessage] [varchar](500) NULL, [Subject] [varchar](50) NULL, [date] [smalldatetime] NULL, [Checked] [tinyint] NULL, [deletedbySender] [tinyint] NULL, [deletedbyRecipient] [tinyint] NULL, [IP] [varchar](15) NULL, [folderID] [int] NULL )I am planning on adding a table like this belowCREATE TABLE [dbo].[tblMessage_folders] ( [folderID] [int] IDENTITY(1,1) NOT NULL, [userID] [int] NOT NULL, [folderName] [varchar](50) NULL, [dateCreated] [smalldatetime] NULL, )Any differing opinions, or anyone agreeing with me I would love to hear your opinions. I'm just want to be sure this doesnt create any problems I might not be seeing.Thanks once again!!mike123 |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2008-06-10 : 13:13:01
|
| Might be nice to add ParentFolderID to tblMessage_Folder to support a directory tree of nested folders. Just a suggestion. Also, it may prove problematic to have UserID in the Folder table, as anytime you create a new user you will have to manually "prime" that table with an FolderID of 0 to ensure they have an inbox. That will do away with the identity as well... in this case you would have composite PK on (UserID, FolderID) instead. I think the UserID could be moved to tbl_Message and then you are free to create a normalized tblFolder (FolderID, FolderName) and relate through tblMessage_Folder (FolderID, MessageID). You already have MessageTo which I assume can be resolved to a UserID.Nathan Skerl |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-06-11 : 00:33:47
|
| definately a good suggestion .. not sure its needed at this point but I think adding in the future wont be a problem..regarding your design, I do see the problems with my design and like where you are going with this..I want users to be able to create folders, and list what folders they have created .. How will this be possible if I move out the userID of the folder table ? Thanks very much!mike123 |
|
|
|
|
|
|
|
|
modifying db design (internal message system)