Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    13

    Unanswered: PostgreSQL Database (Create)

    Hello,

    well probably you will notice that I'm new to this forum. Therefor if I've posted in the wrong section, please accept my appology.

    I needed to create a database for a project to create a blog. So I have created a small ER-Diagram with the following syntaxis:
    - The ellipse is an attribute.
    - The box is an entity.
    - The diamond is a relationship.

    - Every text that has an underline is a key of the corresponding attribute.

    - One user can only have one blog
    - One blog can have multiple pictures
    - One blog can have multiple posts
    - One post can have multiple comments
    - One picture can have multiple comments

    If some things in my ER-Diagram is unclear or if I missed some explaination of the syntaxis please do tell.

    My questions are:

    Is this a "good" ER-Diagram? (With "good" defined as optimized, meaning: optimized for space preserving, optimized for searching through posts, no invalid constraints and consistent; If not: please give me some tips and hints so I can optimize this ER-Diagram)

    Is this ER-Diagram in the third normal form? (I am not sure, but I think it is in third normal form)

    If you have any suggestions please tell me, I very appreciate it.

    Here are some questions after finishing the ER-Diagram and convering it into PostGreSQL code:

    How can I convert this correctly?

    (I normally convert it as follows:
    - The entity will be a table with the attributes corresponding with the entity
    - The relation will be a table with the key attributes of the two corresponding entities. [I think the relation table will have a set of keys am I right?])

    I am a bit familiar with SQL but I'm far from being a pro.

    Thank you for your response!

    Here is an link to my ER-Diagram: http://img89.imageshack.us/img89/2352/vxjifiekdkf.png
    Attached Thumbnails Attached Thumbnails ERD2.PNG  
    Last edited by loquin; 02-24-09 at 18:44. Reason: prefer for images to be uploaded

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Per the 'ERD' you have the user making comments, not the blog having comments.

    Often, rather than showing attributes as separate from the entity, they are shown as a part of the entity - contained within it. Ref the attachment, below.

    However, it's certainly understandable as is. It just appears to be more complex than it actually is, when you're used to viewing them differently!

    Now, as far as the implementation. Yes, the entity would be a table. The attributes are the fields. The primary key of each table is the underlined attribute.

    In a 1:N relationship, the foreign key relationships are defined as a foreign key constraint, within the 'child' table. Only in a many-to-many relationship do you need to use a separate intersection table. In that case the intersection table holds the PK from each of the two related tables, with the PK of th intersection table consisting of both the IDs, and each ID defined with a foreign key constraint pointing back to its related table.

    In this case, defining tables as parent-child, the user table is the parent, the blog table is the child. The blog table would need to have a username or userid field to be used to join the two.

    Likewise, the blog table is a parent of the Posts table. So, the Posts table would need a blog_id field for the relationship.

    Likewise, you would need a blogID in the Pictures table.

    Finally, for the comments. I believe that the comments should be a child of the blog. So, the comments table would need a blogID to relate the comment to the blog. But, since it is made by a user, you would also need a userid field to define who made the comment.
    Attached Thumbnails Attached Thumbnails ERD3.JPG  
    Last edited by loquin; 02-24-09 at 19:25.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Feb 2009
    Posts
    13
    Let me see if I understand. Do you mean that posts and comments should be only in relation with the user?

    In a 1:N relationship, the foreign key relationships are defined as a foreign key constraint, within the 'child' table. Only in a many-to-many relationship do you need to use a separate intersection table. In that case the intersection table holds the PK from each of the two related tables, with the PK of th intersection table consisting of both the IDs, and each ID defined with a foreign key constraint pointing back to its related table.
    Thank you, I totally understand this part.

    PS: Am I correct if I am saying that your approach is the UML approach?

    PS: Thank you for your reply.

    Edit:

    I intended not to let users to have blogs that are named the same or that the user created two of the same usernames. Therefor I used them as a PK. Furthermore, as you said here:

    Per the 'ERD' you have the user making comments, not the blog having comments.
    I understand that this is better, but wouldn't it be much better if I relate user and posts, user and blog, user and comments? Instead of linking user with blog and blog linking to posts (same goes for pictures). How do I decide what to link with user and what to link with blog?

    PS: how can you make the one to many relationship icon to display in Microsoft Access 2007?
    Last edited by temp_tsun; 02-24-09 at 20:28.

Posting Permissions

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