SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    325
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    database design question

    ok i have 3 tables in a db, the first two are this:

    authors:

    +------------+-------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +------------+-------------+------+-----+---------+----------------+
    | authorID | int(11) | | PRI | NULL | auto_increment |
    | authorName | varchar(50) | YES | | NULL | |
    +------------+-------------+------+-----+---------+----------------+



    and

    category:

    +---------+-------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+----------------+
    | catID | int(11) | | PRI | NULL | auto_increment |
    | catName | varchar(30) | YES | | NULL | |
    +---------+-------------+------+-----+---------+----------------+



    now my question is about the third table, articles:


    +-----------+--------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +-----------+--------------+------+-----+---------+----------------+
    | articleID | int(11) | | PRI | NULL | auto_increment |
    | issue | varchar(20) | YES | | NULL | |
    | authorID | int(11) | YES | | NULL | |
    | catID | int(11) | YES | | NULL | |
    | subcat | varchar(50) | YES | | NULL | |
    | title | varchar(50) | YES | | NULL | |
    | subhead1 | varchar(100) | YES | | NULL | |
    | subhead2 | varchar(100) | YES | | NULL | |
    | specs | text | YES | | NULL | |
    | body | text | YES | | NULL | |
    +-----------+--------------+------+-----+---------+----------------+

    i dont know about foreign keys, should i have designated the "authorID" and "catID" columns in the authors table as foreign keys? or is it ok the way i did it?
    From here on, it's instinctual...even straight roads meander.

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    If you ALWAYS have an author, I would create a foreign key constraint on authorid. If you ALWAYS have a category, I would create a foreign key constraint on catid.

    But that's just me. I like to be sure my relationships are intact...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    325
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well i won't always have an author, but i will always have a category.

    What i wound up doing was indexing the authorID and catID fields.
    From here on, it's instinctual...even straight roads meander.


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
  •