SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot ohnnyj's Avatar
    Join Date
    Jun 2003
    Location
    California
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Setting Up Tables

    Hello all:

    I have been waiting to ask this question for a long time and hope someone has a good answer. When creating a set of tables when is is a good idea to use a relationship table to link two tables together. For instance lets say that I have one table that has user information and in that table I have an access_level field. Then I have another table called access with a field of the same name. Why would it be necessary to create a linking table when you could simply populate both w/the same value. Another example might be in having a CMS where each entry has a category_id that corresponds to a category in another table of categories.

    I think I am just lost on the benefits of such separation. If anyone could help me out that would be great.

    Thanks,

    John

  2. #2
    SitePoint Enthusiast MadDog31's Avatar
    Join Date
    Nov 2003
    Location
    Wilmington, NC
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've always understood it as if there's a many-to-many relationship, then you would use a linking table to tie multiple tables together. These multiple tables are used to turn the many-to-many to one-to-many relationships. Am I correct on this assumption?

    Ian
    "It's way better to have 100 idiot clients than to have one idiot boss."

  3. #3
    SitePoint Zealot Drew630's Avatar
    Join Date
    Nov 2001
    Location
    Maryland
    Posts
    175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    John,
    An excellent question to ask. What you are referring to is the normalization of tables. Without delving too far into RDBMS, the idea behind normalization is to presever data intergrity in the database. For example, say you have two tables, tblUsers and tblAccess. Each user is assigned a certain access level. Now, in the tblAccess table, there are records that have a unique accessID and the name of the access, etc. IN the tblUsers table, there is a field called userAccess that is a foreign key to the tblAccess table cooresponding to the record for that level of access.

    tblUsers
    userID userAccess userName etc.
    ------------------------------------
    001 acc1 John
    002 acc 2 Drew

    tblAccess
    accessID AccessName
    ----------------------------
    acc1 Level 1
    acc2 Level 2
    ... etc

    You can see by the illustration that the userAccess field is the foreign key in tblUsers. Now to retain our data integrity, we create a realationship between the two tables. Not another relationship table, just a relationship. The relationship is basically a rule that says tblUsers is linked to tblAccess by linking the UserAccess field to the accessID field.

    If I haven't lost you, here's the reason why we do this. taking the example given, we could just use one table, tblUsers, and have an access Name field that states the level of access. Maybe there are a couple other fields that lare on/off privelages.

    tblUsers
    userID userAccess userName Write Delete Add
    --------------------------------------------------------
    001 Level 1 John No No No
    002 Level 2 Drew No Yes Yes
    003 Level 3 Bob Yes Yes Yes


    Now, for a couple of records, thsi could work. But say down the road, Level 1 access, we want to have them be able to have Add access. If we use just one table like above, then we have to go through each record with Level 1 Access and change the field. The larger the amount of records, the more prone you are to making a mistake. If you forget one or two people, suddenly useraccess is different, despite being the same level, and you have a real headache. But, if you have the sperate tables with the relationship, all you have to do is go into the tblAccess and change the record for Level 1 and you are done. This is also handy with adding new feature and deleting others.

    This is a very simple example, and properly modeling a dtabase can get difficult in a hurry. But I hope this answers your question somwhat as to the advantages of relationships.

    Drew
    ~Drew

    There Is No Greater Joy Than Soaring High On The Wings Of Your Dreams, Except Maybe The Joy Of Watching A Dreamer Who Has Nowhere To Land But In The Ocean Of Reality.

  4. #4
    SitePoint Zealot ohnnyj's Avatar
    Join Date
    Jun 2003
    Location
    California
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both for taking the time to answer my question and I think I am beginning to understand the relationship construct. However, I am still a bit unsure as to when I should create a separate table completely to link two other tables.

  5. #5
    SitePoint Zealot Drew630's Avatar
    Join Date
    Nov 2001
    Location
    Maryland
    Posts
    175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, the thing to do when laying out a new database, is to think of your collection of data as a collection of objects. Take a piece of paper and pencil and list all the different things you want/nee4d to store in your database. For example, you may want to have a library database. So this might be the start of your list:

    Books
    Authors
    Subject
    ... etc.

    you want to list the pieces that make up what you are treying to capture in data form. This often lends to a simple list of tables. Since there are books, we'll have a book table. And since books have authors and we want to know more than simply the name of the author, we need to have an author table. Also, books can be categorized by subject. while this can be a property of a book record, you could also want to know more details about different subjects, so it may warrent its own table.

    This is a really simple example, but it showcases a way to determine your tables. The best thing to do, is layout the tables as you would see fit, including all the different columns you would have. Then look at each table and see if:

    a.) you have the same data listed in multiple tables.
    b.) you have a lot of information that is not relevent to the table. (ie, you have authro information in a table for books)
    c.) you have more than one column dedicated to a particular subject (ie, you have author name, autor birthdate, author hometown, etc.)

    This is the simplest way to explain normalization. OBviously, we only scratch the surface here, and I am not going to get into drawing ER diagrams and normalizing to the 3rd degree. I would suggest finding a book on databases to acquire this info. Here is a link to some basic concepts, but I would seriously look into a book if you want to get into good design.

    Hope this helps.

    Drew
    ~Drew

    There Is No Greater Joy Than Soaring High On The Wings Of Your Dreams, Except Maybe The Joy Of Watching A Dreamer Who Has Nowhere To Land But In The Ocean Of Reality.

  6. #6
    SitePoint Zealot Drew630's Avatar
    Join Date
    Nov 2001
    Location
    Maryland
    Posts
    175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also, remember that a relationship is not another table. A relationship is simply that: a linking between the tables. There is table 1, table 2 and the link. No table 3. There are rare cases where a third table comes in but I can';t think of a good example without looking into my database book and that is at work right now, while I am not.
    ~Drew

    There Is No Greater Joy Than Soaring High On The Wings Of Your Dreams, Except Maybe The Joy Of Watching A Dreamer Who Has Nowhere To Land But In The Ocean Of Reality.

  7. #7
    SitePoint Zealot ohnnyj's Avatar
    Join Date
    Jun 2003
    Location
    California
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I must thank you for your time in answering my question. I know you didn't have to but I appreciate i very much!

    - John


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
  •