Results 1 to 4 of 4
  1. #1
    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.

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  4. #4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •