| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2005-01-31 : 19:46:37
|
| Hi,I have a table of 'Web Pages'. The pages are structured into a hierarchy.Pages=====PageID int pkPageContent ntextParentID int fkThere are 6 top level pages and their ParentID is set to null. Each top level page is associated with a distinct style sheet and all children of that top level parent use the same style sheet. The top level pages cannot be changed. i.e you can't add or delete a top level page.The file names of the six style sheets should be in the database but the question is, where?If I add a column to the Pages table to hold the name of the style sheet then this column will only be used by 6 rows and always left null for all the other perhaps hundreds of pages. I think that strictly speaking, this would stop the table from being in 1st normal form.But maybe that's ok in this case. Any ideas? |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2005-01-31 : 19:49:55
|
| Maybe something like:StyleSheet ( StyleSheetName PK, path, etc..)PagesStyleSheet ( ID PK, StyleSheetName FK, PageID FK) |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-01-31 : 20:22:53
|
| Yes of course. Thanks. A join table doesn't only mean many-many. It can also enable an optional association. |
|
|
|
SqlStar
Posting Yak Master
121 Posts |
Posted - 2005-02-01 : 04:29:32
|
| Hi,Add a "StyleSheetID" column in "StyleSheet" table and make it as primary key instead of having "StyleSheetName" column. Refer this newly added column in "PagesStyleSheet" table.I hope this may give some better performance.:) While we stop to think, we often miss our opportunity :) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-01 : 07:32:48
|
quote:
Add a "StyleSheetID" column in "StyleSheet" table and make it as primary key instead of having "StyleSheetName" column
Why? All it would do is add an uneeded column to a table, and there's no need to create multiple tables anyway. All you really need for this solution is:CREATE TABLE Styles (PageID int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES Pages(PageID),StyleSheet varchar(100) NOT NULL) |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-02-01 : 09:50:26
|
| robvolk, I see that this works but strictly speaking, doesn't it contradict 2nd normal form?What if I one of the top level pages were to be deleted ( I know I said you can't, but if you did ) then you'd also loose a style sheet entity from the database.Furthermore, what if two top level pages were to use the same style sheet. (Again, I said they'd always be distinct, but what if...) Then you'd have the same style sheet Url stored twice. |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2005-02-01 : 10:20:31
|
| You could have a StyleSheet table that would only have 1 column and then the Styles table would look like this. Then you would be in 2nd normal form.CREATE TABLE Styles (PageID int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES Pages(PageID),StyleSheet varchar(100) NOT NULL FOREIGN Key REFERENCES StyleSheets(StyleSheet))Dustin Michaels |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-02-01 : 12:56:56
|
| That looks like ehorn's join table again. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-02-01 : 14:02:32
|
quote:
Originally posted by X-Factor
robvolk, I see that this works but strictly speaking, doesn't it contradict 2nd normal form?
How can a table with a single column primary key have a partial dependency?Jay White |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-01 : 21:58:49
|
OK, gonna answer in reverse order, just to be different  quote:
Furthermore, what if two top level pages were to use the same style sheet. (Again, I said they'd always be distinct, but what if...) Then you'd have the same style sheet Url stored twice.
Vs. storing the same ID number twice? You're still duplicating SOME data. I still don't see any advantage in creating a new table to hold useful data, when you could just put that useful data directly into the table you need. If you wanted to track stylesheets completely independently of your other web pages, then it would make sense...maybe. Which leads to...quote:
What if I one of the top level pages were to be deleted ( I know I said you can't, but if you did ) then you'd also loose a style sheet entity from the database.
I'd say a stylesheet by itself is pretty useless, especially in your app. You're storing stylesheets as an attribute of another page, not as a page/entity unto itself. Lose the web page, and the stylesheet is no longer meaningful. And if it WAS another kind of web page, then it would rightfully belong in the SAME table as the regular web pages.quote:
robvolk, I see that this works but strictly speaking, doesn't it contradict 2nd normal form?
If I were Chris Date I'd worry about that...just before I committed suicide for being Chris Date. Seriously, I don't recite the definitions of the normal forms, and I don't worry too much if I design a table that somehow "violates" one or more of them. The worst thing I can see happening is you might have to update 2 or 3 rows, instead of just one. |
|
|
|
|
Table Design Question