| Author |
 Topic  |
|
|
grazman
Junior Member
USA
193 Posts |
 Posted - 09 September 2000 : 11:49:34
|
Ok, this one is a little longer to explain and possibly harder to explain but will yield some big payoffs. I'll use the REPLY table as my example but the same logic applies to the TOPIC and FORUM table as well.
The primary key for the REPLY table is CAT_ID, FORUM_ID, TOPIC_ID and REPLY_ID. This is called an "Identfying Relatioinship". It means that the primary keys of all the parent tables are included in the primary key of the child table.
I'd like to suggest that the primary key of the REPLY table should be REPLY_ID and only REPLY_ID. TOPIC_ID should be an attribute. It should be a foreign key and it should also be indexed. FORUM_ID and CAT_ID should be removed from the REPLY table. They can be found from joining through the other tables. I don't think a reply needs to know what forum or category it is in.
A further example is on the TOPIC table. It's primary key should be TOPIC_ID. FORUM_ID should be a foreign key that is indexed. CAT_ID should be removed from the TOPIC table.
This will give these forums a number of benefits. The first is to remove all the triggers and code that cascades category or forum changes down the chain of records. If you want to move a topic to a new forum, you merely change the FORUM_ID for that topic. No changes are required to the REPLY table at all. If you want to move a FORUM to a new category, you merely change the CAT_ID for the forum. No changes are required in the TOPIC or REPLY table.
Another benefit is simpler joins. Each table can be joined to it's parent table with one field. Currently to join TOPIC and REPLY you need to join on CAT_ID, FORUM_ID and TOPIC_ID.
You will also find your ASP is cleaner. If each record has a single field primary key, you will be passing fewer parameters around between your pages.
I realize this is a much larger change. The current way certainly isn't wrong. Many systems are designed that way usually for performance sake. I think in this case we can achieve the same or probably better performance by constructing smarter indexes. Especially on last post date fields.
Ok, my last edit to this: This will also dramatically reduce the number of keys. The REPLY table table has 6 indexes on it all based around the 4 primary keys. Every record that is inserted must update all 6 of those indexes. Follow my suggestions here and you'll have 2 indexes for the same table. Your inserts should be almost 3 times faster. Ok, enough of a rant :)
<font color=blue><b>SQLTeam.com</font id=blue></b> - For SQL Server Developers and Administrators
Edited by - grazman on 09 September 2000 11:54:17
Edited by - grazman on 09 September 2000 12:10:40 |
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 09 September 2000 : 12:20:23
|
I totally agree. I never really understood the multi-field keys in the db myself. I think there was some discussion of this in the old Snitz forum.
======
Doug G
====== |
 |
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 11 September 2000 : 08:31:57
|
I would agree with the Reply keys, but I'm afraid I must disagree with the Topic keys. I am planning on adding a mod very soon which will allow you to see when a topic has been moved from one forum to another, and we'll need the forum and category ids as keys because that way we can have multiple topic ids on the table.
Dave Maxwell
--------------
When's the next meeting of Snitzaholics Anonymous<img src=icon_smile_question.gif border=0 align=middle> |
|
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 11 September 2000 : 09:30:59
|
Interesting idea. Curious what you mean by: <BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>and we'll need the forum and category ids as keys because that way we can have multiple topic ids on the table.<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
I'd be interested to see your proposed table layouts if you get a chance.
<font color=blue><b>SQLTeam.com</font id=blue></b> - For SQL Server Developers and Administrators |
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 11 September 2000 : 13:31:32
|
Really the change to allow this is minor. A new date/time field would need to be added and I would just create a new status value for the topic, 2 = moved.
When someone moves a topic, the "original" topic id would be saved with a date/time (to show which replies to still display) and change the topic status to 2 for moved. I would then create a "new" topic record with the new forum and possibly new category id attached.
<b><u>Example:</u></b> you have a topic which has a record like this:
Category ID = 1, Forum ID = 2, Topic ID = 4
Someone decides to move this topic to a different forum, you would then need these records:
Category ID = 1, Forum ID = 2, Topic ID = 4
Category ID = 1, Forum ID = 4, Topic ID = 4
Under your plan, I can't do this because you can't have more than one topic_id 4's. The same would apply if I moved from Category to Category...
Hope this explains it a little better..
Dave Maxwell
--------------
When's the next meeting of Snitzaholics Anonymous<img src=icon_smile_question.gif border=0 align=middle> |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 11 September 2000 : 15:53:21
|
I would use a new table to keep track of moved topics, off the top of my head without thinking much (normal for me!)
<pre id=code><font face=courier size=2 id=code>
ID autonumber
TopicID The topic ID in question
Date Date moved
OldForum The old forum ID
NewForum The new forum ID
Reason A comment if desired
</font id=code></pre id=code>
You can then select from this table by topic and find it's history.
Just my ( $1.00 / 50 )
======
Doug G
======
Edited by - Doug G on 11 September 2000 22:46:17 |
|
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 11 September 2000 : 17:26:34
|
Ah I understand now. Actually I think I'd prefer to have that in a separate table also. I'd prefer to have each topic in the table once. It seems like a much cleaner solution.
If you really want to do this without adding another table I'd suggest either the "moved to" topic gets a new TOPIC_ID or the "moved from" gets a new TOPIC_ID. Which is essentially what you are doing.
Actually as I look at it right now, TOPIC_ID is an IDENTITY column in SQL. This means it should be unique. However the unique physical index is assigned to CAT_ID, FORUM_ID and TOPIC_ID.
Overall, I stand by my earlier suggestion. I think the data model would be much simpler if each record were identified by a single field key. Each record would also have a foreign key that pointed to it's parent (i.e. TOPICS is identified by TOPIC_ID and FORUM_ID is a foreign key back into the forum table.)
I think a cleaner solution to what you want might be to store a PREVIOUS_FORUM_ID also as a foreign key. If you need multiple topic moves, you can add a subtable to hold each move as described by Doug G. I think this is a much, much cleaner solution.
Again, only my small number of cents on the matter.
<font color=blue><b>SQLTeam.com</font id=blue></b> - For SQL Server Developers and Administrators |
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 12 September 2000 : 08:02:58
|
The only problem with the new table would be when you try to incorporate it into the paging logic. You'd need to join the tables which would cause the forum to slow down because joins are typically slower than single reads. I understand the new table would be "cleaner" but in effect it would have a negative effect on the performance of the overall system.
Overnormalization can be bad sometimes, but that's just MHO.
Dave Maxwell
--------------
When's the next meeting of Snitzaholics Anonymous<img src=icon_smile_question.gif border=0 align=middle> |
|
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 12 September 2000 : 09:13:30
|
Hey dave, good points. However, I think the performance hit will be minimal. My comments should apply equally well to both SQL Server and Access. My Access skills are fair at best take that part with a skeptical eye.
<ul><li>It should be relatively easy to flag any moved record. After that you can only join to them if you need to.</li>
<li>The SQL Server and Access database engines where specifically designed for joins. They've spent years optimizing exactly what you want to do</li>
<li>Right now the TOPIC table has a 12 byte primary key. I think reducing this to a 4 byte primary key will save more reads than will be added by the second table. The REPLY table will get even more benefit. Reducing the extra indexes from the unneeded primary key (CAT_ID) will save further processing.</li>
<li>Currently the main page (default.asp) and the active topics (active.asp) are generating TABLE SCANS. They aren't even using the indexes. Totally ignoring them. Until those queries can get optimized nothing much else we do will really have a huge effect performance-wise. A table scan is typically 5 to 10 TIMES slower than an indexed search. And the bigger your table gets, the slower the query gets.</li>
<li>I'm fine to denormlize data for performance. I'm less happy to denormalize your key structure for performance. I'd rather see you store a flag in the TOPIC table saying this record has been moved. That will allow you to determine moved records in a single table query but not complicate your key structure with duplicate entries in an identity column</li>
<li>Under your plan, what if someone moves a TOPIC and then moves it back? I don't see how the data structure supports that. Doug's approach will allow multiple FORUM and TOPIC moves while still keeping the main tables "clean". Also, if you store moved TOPICs in the main table you have to parse them out when you display the main forum thus hurting performance.</li>
<li>I think the approach should be to optimize the DB design for what you do most and based on my site that is display the default page. Even if we were tracking moves, I'd only want some type of a link or indicator that this topic had been moved. I think we can design so that doesn't require a second table read.</li></ul>
Ok, that's got to be worth at least 2.5 pennies :) Or maybe less depending on your opinion :) Can we please, please get a bigger text box to type in :)
<font color=blue><b>SQLTeam.com</font id=blue></b> - For SQL Server Developers and Administrators |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 12 September 2000 : 23:32:10
|
There is performance and then there is performance.
In an asp environment, doing two independent single table queries use considerably more resources (or are at least much slower) than a single query with even multiple joins. The two queries are queued up in the ado connection, and the speed hit happens outside the database server. Setting up connection pooling helps a lot, but still the time it takes to setup the connection to get to the db server is a much bigger factor in sluggish sites.
======
Doug G
====== |
|
|
| |
Topic |
|
Topic Locked
