SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict sedna's Avatar
    Join Date
    Jan 2006
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help with relational databases

    hey all,

    i was wondering how would i overcome this?

    the situation

    i am the process of making a site that will allow people to upload phpbb hacks that they have created.
    i am going to implement a user system so that only registred users can upload hacks to the site i have planned out the database well kinda and i have decide that each hack category will have it's own table for example hacks that moddifiy the way the admin panel looks will be in a table called admin_hacks and hacks that change the user panel will be in a table called user_hacks and so on.

    within the user login system each hack author wil have there own profile page with the list of hacks that they have submmited, well that is the probelm how do i go about showing what hacks an author has uploaded within there profile?

  2. #2
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    Devon, UK
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Different tables for each category will be a maintenance nightmare and your code will be far more difficult. Put every entry into a single table and add a 'category' column (make sure it's indexed). You can then use a single select to grab all the entries per category, user or whatever.

  3. #3
    SitePoint Addict sedna's Avatar
    Join Date
    Jan 2006
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what do you mean by indexed, as i am new to php and mysql

  4. #4
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    Devon, UK
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You use KEY `idx_category` (`category`) in the CREATE TABLE command. MySQL will then index the category column effectively, so if you asked for all entries for category1, MySQL would return them quickly.

  5. #5
    SitePoint Zealot Bill Palmer's Avatar
    Join Date
    Oct 2005
    Location
    London, UK
    Posts
    148
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are new to designing database schemas, I recommend researching database normalization. Most people normalize to the third normal form.

    I recommend this article as well.

    Avoiding redundancy will make your life a hell of a lot easier in the long run.

    well that is the problem how do i go about showing what hacks an author has uploaded within there profile?
    SELECT * FROM hacks WHERE author_id = X ORDER BY time DESC

    (where X is the id number of the author from the 'authors' table)

    That query will select every hack a particular author has created ordered from newest to oldest.

  6. #6
    Non-Member
    Join Date
    Dec 2006
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    There are several potential solutions to this design, depending upon whether you want to steer the design towards maintainability vs. performance. Let me give you one potential design:

    Author's Table [One row for each author]

    Author's ID - computer generated (index on this column)
    Author Name
    Authors Info ... etc.

    Author's Hacks Table [There is one row in this table for each hack by each author. This handles the one-to-many relationship between Author and Hacks]

    Author's ID/Author's Hack ID (Compound index on these two fields)

    Hack Table [One row for each hack]

    Hack ID - computer generated (index on this column)
    Hack description ... etc.

    You have to do a three-way join with this solution, but it eliminates the problem of having to replicate the Author's ID in each Hack Table row (this would be redundant and non-normalized).

    Hope this helps.

    Rich


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
  •