If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > database design: join or keep split

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 06-27-09, 11:24
Simon_Verhoeven Simon_Verhoeven is offline
Registered User
 
Join Date: Jun 2009
Posts: 3
database design: join or keep split

I'm going to make an asp.net site backed with a database.
Now I'm having a bit of doubts about the design I've made and some questions.

Here's the design for now: http://simonverhoeven.be/images/database%20design.jpg

An arrow indicates a relation.

If I write my own login system and I don't use the default asp.net login control then I can merge the users and userinfo table.
It is also possible to merge the articles and articleContent tables.

Now the reason I've split the articles and articleContent is that I'm planning on doing the following:
-use the first table to create a page with a short summary and the second one will contain the html page in the content field. (I'll be using master pages)

Now my doubts:
-will splitting these decrease loading times or am I way off?
-will the increased database usage by the content field offset the gain of having less pages?
-and I'm thinking about tracking the post count/article. Now when does using an extra field outperform a join with a count function. Or should I just choose one of the both and stick with it?

I would also like to track the comment count of a user, here I have the same doubts for posts/user.

Thank you in advance.
Reply With Quote
  #2 (permalink)  
Old 06-28-09, 15:22
dportas dportas is offline
Registered User
 
Join Date: Dec 2007
Location: London, UK
Posts: 732
Quote:
Originally Posted by Simon_Verhoeven
If I write my own login system and I don't use the default asp.net login control then I can merge the users and userinfo table.
It is also possible to merge the articles and articleContent tables.
Your ER diagram is not very clear because it doesn't use any of the standard ER modelling notations.

Is there just exactly one question, answer and email per userid? If yes then I would expect those attributes to go in a table with userid as the key (users).

Is there just one "content" per articleid? If yes then I would expect that attribute to go in a table with articleid as the key (articles).

The principle I'm applying here is called Boyce Codd Normal Form, which roughly speaking requires that every non-key attribute should depend on table's candidate key(s). Use that rule as your guide, and study a book or take a course on database design if it's not familiar to you.
Reply With Quote
  #3 (permalink)  
Old 06-28-09, 15:25
dportas dportas is offline
Registered User
 
Join Date: Dec 2007
Location: London, UK
Posts: 732
Tip: Don't call any column by the name "password". For obvious security reasons passwords should always be hashed and not stored in retrievable form. Compliance auditors will home in on any column called password and may suspect it as a security flaw. If you hash the password and call the column "PasswordHash" then the intended meaning is immediately much more transparent.
Reply With Quote
  #4 (permalink)  
Old 06-28-09, 17:48
Simon_Verhoeven Simon_Verhoeven is offline
Registered User
 
Join Date: Jun 2009
Posts: 3
Sorry if it isn't clear.
Black = tablename
arrow = indicates a relation and the direction
The first row of every table is a PK
If it has an arrow attached to it, the side with the line is a FK.

Is there just exactly one question, answer and email per userid?
Yes

Is there just one "content" per articleid? If yes then I would expect that attribute to go in a table with articleid as the key (articles).
Yes

I've learned some database design and system design at school but sadly they're a bit basic and don't really cover which method is better.

And I've already tried to normalize the table using Codd, my concerns now are more about which way will perform the best and if I didn't miss anything.

Thank you already.

Dportas, I'll be using md5 + salt, but thanks for the tip.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On