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 |
|
Ned
Starting Member
16 Posts |
 Posted - 2003-10-03 : 12:14:48
|
| Hi,I have a web application where I store information about a page in a database. Upon rendering of the page I query the database (providing the pagePath) for its title, description and a list of security roles that can access this page.I have a few questions:Its natural key is the pagePath but it can get quite long. Is it still a good idea to use this as the primary key or should I have a int pageID? This table needs to join to a PageRoles table where a page is associated with one or more security roles (int). If I use the pagePath as the key, then I need to use the pagePath as part of a compound key for the PageRoles table. (Is that still preferred even if the field is somewhatlong.)There are different types of pages. Some pages I need to display in a list (such as available reports) so a sortOrder column would be preferred. Most of the records don't need this functionality. Is it ok to add a sortOrder column even if only some of the records need it? Or should this be broken into a seperate table. If they are in a seperate table, I still need to join to the PageRoles table for security options.CREATE TABLE [dbo].[Pages] ( [PageID] [int] NOT NULL , [PageType] [varchar] (10) NOT NULL , [PagePath] [varchar] (100) NOT NULL , [Title] [varchar] (50) NOT NULL , [Description] [varchar] (1000) NOT NULL , [Visible] [bit] NOT NULL , [SortOrder] [int] NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[PageRoles] ( [PageID] [int] NOT NULL , [RoleID] [int] NOT NULL ) ON [PRIMARY]GO |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-03 : 14:10:14
|
| Looks good to me...You're creating a surrogate key...just put a unique contraint on pagepath and make sort order nullable...Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
 |
|
|
|
|
|
Design question