SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru D-flyer's Avatar
    Join Date
    Jan 2001
    Location
    Near a computer
    Posts
    782
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi there,

    I am trying to put up a CMS for a article based website.

    During the physical db design i encountered a problem, i think. Here it is:

    I have a tableArticle
    ArticleID <--prim. key
    ArticleTitle
    ArticleTxt
    ArticleDate
    AuthorID <--for. key
    StatusID <--for. key
    ImageID <----????
    ?????= image has [one and only one | one or many] article, so can I put the ImageID as a foreign key in tableArticle.

    What am I doing wrong?

    Greetz
    D-flyer

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I don't understand your problem. If I read right, you need to specify one image for each article, but a single image may be used by multiple articles. If that is the case, then the structure for the article table you quote above will be fine as-is.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    SitePoint Guru D-flyer's Avatar
    Join Date
    Jan 2001
    Location
    Near a computer
    Posts
    782
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Kyank (again),

    It's exactly the other way around, i mean:

    1 One article has one or many (single) images
    2 A single image may be used by one article

    So there be no double images in the website.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Rawalpindi, Pakistan
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    I am not a db guru but I wud have done .......


    create a table say articleImage with articleId (Foriegn key to the Article table, and imageId (Foriegn key to the image table).

    Both of them make composite primary key of articleImage table.

    Aatif Malik

  5. #5
    SitePoint Guru D-flyer's Avatar
    Join Date
    Jan 2001
    Location
    Near a computer
    Posts
    782
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thx Aatif,

    I have thought about your solution, but this implies a many-to-many relationship and i want to (must) have a one-to-many.

    That is i think i want this, am a newbie and i'll make mistakes.

    D-flyer

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Rawalpindi, Pakistan
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    make imageID primary key, or unique, that will stop many to MANY...

    Thats what I thought, dont know if it is exactly the answer.......

    any way........

  7. #7
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    table Articles
    ---------------------
    ArticleID <--prim. key
    ArticleTitle
    ArticleTxt
    ArticleDate
    AuthorID <--for. key
    StatusID <--for. key


    table Images
    ---------------------
    ImageID <-- prim. key
    ImageName
    ArticleID <-- foreign key

    That's all you need.

  8. #8
    SitePoint Guru D-flyer's Avatar
    Join Date
    Jan 2001
    Location
    Near a computer
    Posts
    782
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys,

    I am now going to build the mysql-tables, well almost.

    I realised the articles aren't exactly articles they are more like reviews. So i have to make more tables, right?

    Let's say i have written a review of a book. Then i am the author of the review but there also a author of the book, does this mean:

    tbl_Authorbook
    tbl_AuthorReview

    I am getting a little headach
    Sorry
    D-flyer

  9. #9
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your question is moving away from being about technical correctness to being more about analysis of requirements. The answer depends on what it is "in the real world" you are trying to model and what data you need for your model to work. I say this because:

    1) If a book author can also be a reviewer or vice-versa then they are in essence the same entity so you could model this


    Articles
    -----------
    ArticleID
    ... other fields ...
    AuthorID <- foreign key references table Writers
    ReviewerID <- foreign key references table Writers


    Writers
    ---------
    WriterID
    email
    etc

    2) On the other you may need to capture different data for an author than for a reviewer for your data model to work. For example, you may need to know the email address of your reviewer, their phone number etc, but you don't need (or may not have) this information about an author. In this case you are dealing with diferent entities and you would model this way

    Articles
    -----------
    ArticleID
    ... other fields ...
    AuthorID <- foreign key references table Authors
    ReviewerID <- foreign key references table Writer


    Authors
    -----------
    AuthorID
    Name


    Reviewer
    -----------
    ReviewerID
    Name
    email
    Phone
    etc...

    Just my thoughts.

  10. #10
    SitePoint Guru D-flyer's Avatar
    Join Date
    Jan 2001
    Location
    Near a computer
    Posts
    782
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Big thx to u freakysid,

    My datamodel is going to look similar to your suggestion 2.

    As a newbie i haven't layout datamodel often. This will be be my second attempt. After reading differents threads i came to the conclusion that a good datamodel is the basis. That's why i am asking a lot of "analysis of requirements"-questions.

    Your reply help me a lot,

    D-flyer


Bookmarks

Posting Permissions

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