SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    ********* Addict
    Join Date
    Apr 2000
    Location
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here it follows:

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>TABLES..
    ........
    ........


    Articles..
    ..........

    AID - To distinguish articles
    Title - Title to appear on the top of every page
    AuthorID - Links to Authors table which stores profiles etc.
    Published - Date article was first put on the Web.
    Updated - Last date in which article was updated.
    ArticleText - To storep text on any specific article.

    Authors..
    .........

    AuthorID - Assigns each article an ID
    Profile - A brief about the author

    ArticleLookup..
    ...............

    AID - Links an article with ...
    CID - ... a category[/code]

    Do you think this database layout is good? Will it will have any problems with that "anomaly" stuff? Thanks in advance for your feed back and help!



  2. #2
    ********* Addict
    Join Date
    Apr 2000
    Location
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just forgot to think about one thing in this layout: the subcategories. How would I go about making subcategories in just some of the categories (so when you click on some categories it would show up all the articles while others would link to the subcategories)? You can read my previous post here. Again, thanks for your help (and sorry Eric that I didn't understand your explanation).

  3. #3
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not just include a TopicNumber column in Articles? Then with TopicNumber, look up the topic in a "Topics" table. Seems more efficient.

    In both Articles and Authors you have a field named AuthorID. Change one to eliminate all problems when you're SELECTing from multiple tables.

    All I can think of now..

  4. #4
    ********* Addict
    Join Date
    Apr 2000
    Location
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't really get it. I thought most databases needed a lookup table. Won't it be quicker if I have a separate lookup table instead of having that data with the articles?
    As for the AuthorID the purpose of that is so each author has one record and if their record is changed all articles will have their new profile. What did you mean by deleting it from one of the tables?
    Probably everything I said in this post is useless. I'm dumb at this stuff anyway...

  5. #5
    SitePoint Enthusiast Daniel's Avatar
    Join Date
    Dec 1999
    Location
    Belfast
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just thought I'd make a couple of points:

    Firstly you should keep the ArticleLookup table. If you used a TopicNumber column in Articles instead you would not be able to have a book listed in multiple categories

    The lookup table will also allow you to create subcategories. The first thing you should do is assign a number to each category and also to each subcategory. Then, for example, book X could be in category 6 and in subcategories 20 and 23. The database itself will not 'know' the relations between categories and subcategories - 20 could be a subcategory of category 4 and 23 could be a subcategory of category 6. You will need to keep a record of this structure somewhere else. You can then create a different record in ArticleLookup for each category/subcategory the book appears in.

    I would split the Authors table up a bit more. How about having fields such as Name, Email, ICQ, and WebSite? This will keep your data much more ordered than it would be with just a Profile field.

    ------------------
    Daniel Irvine
    F-Free - Free Stuff and Freebies!

  6. #6
    SitePoint Wizard
    Join Date
    Apr 2000
    Posts
    1,483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I agree with d3v - one of the AuthorID columns should be renamed...when you query the database from the webpage it will get confussing if there are two columns with the same name. So rename the field in one of the tables.
    I hope that cleared up what d3v said.

  7. #7
    ********* Addict
    Join Date
    Apr 2000
    Location
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It was simpler than I thought! You mean it should look like this?
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>TABLES..
    ........
    ........


    Articles..
    ..........

    AID - To distinguish articles
    Title - Title to appear on the top of every page
    AuthorID - Links to Authors table which stores profiles etc.
    Published - Date article was first put on the Web.
    Updated - Last date in which article was updated.
    ArticleText - To storep text on any specific article.

    Authors..
    .........

    ID - Assigns each article an ID
    Name - Author's name
    Email - Author's email
    ICQ - Author's ICQ number
    Website - Author's website(s)
    Profile - A brief about the author

    ArticleLookup..
    ...............

    AID - Links an article with ...
    CID - ... a category[/code]
    Thanks for your help so far! Are there still any problems with this layout? Do you think it is an "expandable" layout for a database?


    [This message has been edited by pedro_gb (edited July 23, 2000).]

  8. #8
    SitePoint Wizard
    Join Date
    Apr 2000
    Posts
    1,483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep...that looks pretty good. It should be expandable to a degree...and hopefully it should meet most of your needs for a while!

  9. #9
    ********* Addict
    Join Date
    Apr 2000
    Location
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Phew! That was actually quite easy! Thanks for everyone who helped me, though!

  10. #10
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    To store the category-subcategory structure, you'll need another lookup table. The two columns will store "parent-child" pairs of category ID's. Categories not appearing as "parents" in this table will be your "main categories". Writing the SQL query to list the "main categories" (i.e. listing all categories that do not appear in the "parents" column of the category-subcategory lookup table) will be the hardest part, so here's the query for you. :-)

    SELECT Categories.CID, Categories.Name, COUNT(CatLookup.Parent) AS count
    FROM Categories LEFT JOIN CatLookup
    ON Categories.CID=CatLookup.Parent
    GROUP BY Categories.CID
    HAVING count=0;


    Be sure to read Part 9 of my series to find out how this works! :-)


    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!

  11. #11
    SitePoint Enthusiast Daniel's Avatar
    Join Date
    Dec 1999
    Location
    Belfast
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Pedro:

    You can place an article in multiple categories simply by adding another record in your ArticleLookup table. For example, say article 12 was in both categories 2 and 3. Then you would just need to have the following records:

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    AID CID
    12 2
    12 3
    [/code]

    Also, I would have to agree with temis on the naming issue. Names (especially key fields) should always be kept constant. That was one of the things I was taught about database design anyway Also even though you need to write more code, the code will be more specific and it will therefore be easier for other people to understand when they read it.

    ------------------
    Daniel Irvine
    F-Free - Free Stuff and Freebies!

  12. #12
    ********* Addict
    Join Date
    Apr 2000
    Location
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay... I will have to read part nine then... But now two other problems came in this design. I want some articles to appear in more than one category. Fine. That is not the problem. But I want to have something near the top of the page telling the visitor where he is (like SitePoint has). That is one problem. The second one is: what will happen if the article is in more than one category? Thanks in advance for your help (again)

  13. #13
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    District of Columbia
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    quinn, i disagree with you. Why does the authorID need to be renamed? He's storing an article posted by an Author identified by an authorID (foreign key), then looks up the author details in the authors lookup table, where AuthorID is a primary key. If you rename it will create confusion.

    Category lookup table should solve your problem about telling the visitor where s/he is. You can pass CID as a variable in the URL

    (&lt;a href="pageName.***?CID=***"&gt; or similar)

    and then look up the category name.

  14. #14
    ********* Addict
    Join Date
    Apr 2000
    Location
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This maybe a stupid question but here it goes: is there any way in achieving that effect without having to pass the variable in front of the visitor's eyes but with them still being able to bookmark it?

  15. #15
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Temis:
    It isn't usually a good idea to have two columns named the same in different tables. That means when you do a query like:

    SELECT AuthorID, AuthorID FROM Articles, Authors

    It won't work. You need to do:

    SELECT Articles.AuthorID, Authors.AuthorID from Articles, Authors

    It makes everything much simpler if you can just do...

    SELECT WrittenBy, AuthorID FROM Articles, Authors

    Plus in the articles table, AID and AuthorID can confuse people. They look the same except one is abbreviated.

  16. #16
    ********* Addict
    Join Date
    Apr 2000
    Location
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What will happen if an article is in more than one category? How could the "line at the top that tells the visitor where he is" work?

  17. #17
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The way SitePoint does this is to just look in the database and get a list of all categories that the article you are requesting belongs to, then just picks the first in the list to display. So an article appearing in multiple categories will only ever display the "first" category it belongs to at the top of its article page. Since categories aren't a huge element in the way most users navigate our site, this is quite acceptable. To actually track which category the user selected the article from, however, we would indeed need to pass the category ID.


    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!


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
  •