| Author |
 Topic  |
|
e3stone
Average Member
USA
885 Posts |
 Posted - 05 October 2002 : 16:07:12
|
I installed Visio 2002 last week and I've been playing around with it some. Just for fun, I thought I'd import the snitz database and draw up the ERD. I noticed a few things as I was doing it. Let me start by saying that I don't want to upset anyone 
There are no foreign keys anywhere in the database. Each 'key' is a primary key. For example, in the Forum table the primary key is FORUM_ID & CAT_ID. Shouldn't the primary key be FORUM_ID and the foreign key be CAT_ID? I don't believe a concatenated key is the best solution is this case. For this example, forget that FORUM_ID is an set to an identity field or autonumber, so in theory, there could be one forum that belongs to multiple categories.
FORUM_ID=1
CAT_ID =1
FORUM_ID=1
CAT_ID=2
etc.
If FORUM_ID was the PK and CAT_ID was the FK, then this would wouldn't be allowed. This instance occurs throughout the database.
IMO, the table structure should be:
CATEGORY
CAT_ID(PK)
FORUM
FORUM_ID(PK),CAT_ID(FK)
TOPICS
TOPIC_ID(PK),FORUM_ID(FK),T_AUTHOR(FK)
REPLY
REPLY_ID(PK),TOPIC_ID(FK),R_AUTHOR(FK)
...I've never quite understood why CAT_ID is carried all the way down to the Reply table.
Using the MODERATOR table, there should be a concatenated key:
MEMBER_ID(PK)(FK),FORUM_ID(PK)(FK)
in the current design there could be duplicate entries...in theory. I know there won't be because of the ASP checking, but it just doesn't seem like good database design to me IMO. The foreign key would make sure that the member and forum both exist. What happens with this table if a member or forum is deleted?
This doesn't really seem like it's a database, but more like a bunch of data that doesn't really know that it's related.
It might not seem like a big deal, but I think this is something that we must address. It may involve some code changes, though. With this design, if we want to delete a category, then we'd have to issue about 5 deletes in order to remove all the data, instead of just one. The ability to do that is an issue in itself, though can be bad sometimes.
It just seems that every great database-driven-application has a great database to go along with it. I'm not saying that our database is bad or good, but just not as good as I believe it could be.
Would any of these issues even be on the discussion table, or is it "if it ain't broke..." thing? |
<-- Eric --> |
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 05 October 2002 : 17:40:14
|
quote:
Originally posted by e3stone
...I've never quite understood why CAT_ID is carried all the way down to the Reply table.
If it wasn't then there could be an option to move a forum from one category to another.
Here's one bit of code that would need changing:
post_info.asp, line 178-186, change the T.CAT_ID to F.CAT_ID
strSql = "SELECT C.CAT_STATUS, C.CAT_NAME, " &_
"F.FORUM_ID, F.F_STATUS, F.F_TYPE, F.F_SUBJECT, " &_
"T.T_STATUS, T.T_AUTHOR, T.T_SUBJECT " &_
" FROM " & strTablePrefix & "CATEGORY C, " &_
strTablePrefix & "FORUM F, " &_
strActivePrefix & "TOPICS T" &_
" WHERE C.CAT_ID = T.CAT_ID " &_
" AND F.FORUM_ID = T.FORUM_ID " &_
" AND T.TOPIC_ID = " & Topic_ID & ""
Similar changes in post.asp, topic.asp and active.asp.
There are probably others that would need looking at as well. |
 |
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 05 October 2002 : 19:35:00
|
| If you intend initially to support many DB types then its far easier to enforce relationships via code than via FK's in the DB I suspect thats how/why we have the situation we have today. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
e3stone
Average Member
USA
885 Posts |
Posted - 06 October 2002 : 04:33:28
|
quote:
Originally posted by Gremlin
If you intend initially to support many DB types then its far easier to enforce relationships via code than via FK's in the DB I suspect thats how/why we have the situation we have today.
could you explain this further? I don't understand how a primary key or foreign key is different in various db's? |
<-- Eric --> |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 06 October 2002 : 06:25:49
|
Foreign keys are used to inforce referential integrity across several tables, that is; when foreign keys are used to link one table to another, referential integrity, by its very nature, imposes constraints on inserting new records and updating existing records.
For example, if a table only accepts certain types of values for a particular field, and other tables use that field as their foreign key, this automatically imposes certain constraints on the dependent tables. Similarly, referential integrity demands that a change in the field used as a foreign key - a deletion or new insertion - must immediately be reflected in all dependent tables.
Snitz enforces this integrity via code instead, for what reason I really don't know, but my suspicion is becuase at the time it was conceived not all DB's handled FK's or at least didn't all handle them the same way. bear in mind I'm only guessing here I could be completely wrong about why they're not used. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Deleted
deleted
4116 Posts |
Posted - 06 October 2002 : 13:32:48
|
The referencial integrity use (of secondary indexes) is only one side of the medallion. The other side is performance...
|
 Stop the WAR! |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 06 October 2002 : 15:26:30
|
Is there some problem with the db that will be fixed by adding RI?
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 06 October 2002 : 15:34:29
|
| Is there any reason for the CAT_ID colmun in so many tables? |
|
|
|
e3stone
Average Member
USA
885 Posts |
Posted - 06 October 2002 : 16:05:28
|
quote:
Originally posted by Doug G
Is there some problem with the db that will be fixed by adding RI?
RI is just good design practice. This site seems to be working a lot on good design of code, making the code faster, etc, but not much importance is placed on the db design. This is a place where people learn how to code with ASP and use/design databases, too, since this application has a database. We should try and make every effort to design the database so it not only works, but to design to a certain standard. The db design should be one of the most important design steps, IMO.
You don't just have to look at the RI issues, look at the index design. The FORUM table has 4 indexes. 1)[CAT_ID][FORUM_ID] 2)[CAT_ID] 3)[CAT_ID] 4) [FORUM_ID] I don't really know why CAT_ID is in two seperate indexes by itself. |
<-- Eric --> |
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 06 October 2002 : 16:43:09
|
quote:
Originally posted by e3stone
You don't just have to look at the RI issues, look at the index design. The FORUM table has 4 indexes. 1)[CAT_ID][FORUM_ID] 2)[CAT_ID] 3)[CAT_ID] 4) [FORUM_ID] I don't really know why CAT_ID is in two seperate indexes by itself.
That's just for MSSQL databases. Here's the indexes on the FORUM table for all three types:
ACCESS
PRIMARY KEY (FORUM_ID)
MSSQL
PRIMARY KEY (CAT_ID, FORUM_ID)
INDEX (FORUM_ID)
INDEX (CAT_ID)
INDEX (CAT_ID)
MYSQL
PRIMARY KEY (CAT_ID, FORUM_ID)
KEY (FORUM_ID)
KEY (CAT_ID)
|
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 06 October 2002 : 16:51:12
|
Here's some more for the TOPICS table, as you can see MSSQL and MYSQL both have an index the same as the primary key!
Table: TOPICS
ACCESS
PRIMARY KEY (TOPIC_ID)
MSSQL
PRIMARY KEY (CAT_ID, FORUM_ID, TOPIC_ID)
INDEX (CAT_ID, FORUM_ID, TOPIC_ID)
INDEX (CAT_ID, FORUM_ID)
INDEX (CAT_ID)
INDEX (FORUM_ID)
INDEX (TOPIC_ID)
INDEX (T_AUTHOR)
MYSQL
PRIMARY KEY (CAT_ID, FORUM_ID, TOPIC_ID)
KEY (CAT_ID,FORUM_ID,TOPIC_ID)
KEY (CAT_ID)
KEY (FORUM_ID)
KEY (TOPIC_ID)
|
Edited by - pweighill on 06 October 2002 17:00:25 |
|
|
|
e3stone
Average Member
USA
885 Posts |
Posted - 06 October 2002 : 16:55:52
|
| Yeah, I guess I should've prefaced this by saying that I'm dealing with MSSQL. |
<-- Eric --> |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 06 October 2002 : 21:36:16
|
But is there some problem with data that would be corrected by adding RI to the database? I know of none. Just because RI exists doesn't mean you have to use it, and it is not necessarily a bad design decision not to implement RI in the database. It depends on the circumstances, the data, and the database. It is perfectly acceptable design to have the application program enforce RI rather than the database, particularly in an application that may have different backend db's.
One of the most serious problems with the Snitz database was caused by some trigger-based RI installed in early SQL Server Snitzes. These little RI devils laid dormant for a long time, then a Snitz upgrade that changed some database structure caused a few of these older db's to do a cascade update and put all the replies in their entire forum under one or two topics. This only happened with some of the very earliest SQL Server implementations of Snitz that were loaded from a sql script. Shortly after, the DBS file method of database creation came along and everyone, including the users of the affected forums, forgot about the RI triggers in their db. It was probably a year later before the problem surfaced, but caused a lot of un-fixable database problems, requiring reloads from backups and loss of posts.
And as bozden stated, there is a bit of a performance gain in some cases. |
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 October 2002 : 22:01:17
|
quote:
Originally posted by Doug G
One of the most serious problems with the Snitz database was caused by some trigger-based RI installed in early SQL Server Snitzes. These little RI devils laid dormant for a long time, then a Snitz upgrade that changed some database structure caused a few of these older db's to do a cascade update and put all the replies in their entire forum under one or two topics. This only happened with some of the very earliest SQL Server implementations of Snitz that were loaded from a sql script.
I would risk that the problem was the cascade update and not the referential integrity itself. I use RI all the time, but I seldom use cascade updates and deletes. I like to be in control and I also like the added security that comes from explicitly having to delete related records. It usually avoids, or at least diminishes the probability of big delete mistakes. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 06 October 2002 : 22:13:14
|
| Actually it was a little more complicated than that, iirc the problem only surfaced when the Access Upsize wizard was used to upgrade to MSSQL. The upsize wizard decided to insert triggers to enforce RI (cascading insert/update/delete wasn't even available in MS-SQL until 7.0) |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
Topic |
|
Topic Locked
