SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    relational database?

    I'm having trouble finding clear information on creating a relational database.

    I have 4 fields that have primary key integer fields. The main table also has a user_id, category_id and state_id fields. These names are used as the primary keys for their own respective fields.

    My problem is that I do not understand how they are linked aside from the name. I don't see anyway to connect these together in phpmyAdmin.

    What am I missing?

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,604
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    foreign keys
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If a user lives in a state such as Maryland, and if there's a seperate table for states which defines them such as

    STATES
    state_id state_name
    1 Alabama
    2 Alaska

    You could find the name by using that state_id as a foreign key to refer to the primary key of the states table.
    Cross browser css bugs

    Dan Schulz you will be missed

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looks like I can only use INNODB tables then right?

    The "Relation View" doesn't show up when the table is ISAM. Not a big deal, as the tables have no data in them, but I have to go through the process of creating them again.

  5. #5
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://dev.mysql.com/doc/refman/5.1/...nstraints.html

    InnoDB supports foreign key constraints
    Pretty much, yeah.
    Cross browser css bugs

    Dan Schulz you will be missed

  6. #6
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You don't need to use InnoDB to use foreign keys, you only need to use InnoDB if you wish the database to automatically enforce foreign key restraints.

    Even if you don't enforce foreign key restraints, you can join tables in queries via the foreign keys using ISAM.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    in fact you can join tables in queries on whatever columns you wish, whether they have been defined as keys or not
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Even if you don't enforce foreign key restraints, you can join tables in queries via the foreign keys using ISAM. -Dr John
    Can you explain this more, this all completely new to me. I have been looking around in phpmyAdmin, but don't understand your comment in creating foreign keys for isam. Here is a partial list of my tables, minus two others. The datatypes are my best guess, could be wrong. The resource field is the main one and would be filled by the user input from forms, which in turn is partially fed by sql from the category and state fields by drop menus. Hope that makes sense.

    resource CREATE TABLE `resource` (
    `resource_id` bigint(60) NOT NULL AUTO_INCREMENT,
    `user_id` bigint(60) NOT NULL,
    `category_id` bigint(60) NOT NULL,
    `state_id` tinyint(2) NOT NULL,
    `url` varchar(60) NOT NULL,
    `title` varchar(100) NOT NULL,
    `description` tinytext NOT NULL,
    `rating` tinyint(10) NOT NULL,
    `approved` tinyint(1) NOT NULL,
    `time` datetime NOT NULL,
    `remote_ip` text NOT NULL,
    PRIMARY KEY (`resource_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1


    states CREATE TABLE `states` (
    `state_id` tinyint(2) NOT NULL AUTO_INCREMENT,
    `sCode` char(2) NOT NULL,
    `sName` char(100) NOT NULL,
    PRIMARY KEY (`state_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=61 DEFAULT CHARSET=latin1

    user CREATE TABLE `user` (
    `user_id` bigint(60) NOT NULL AUTO_INCREMENT,
    `email` varchar(60) NOT NULL,
    `username` varchar(20) NOT NULL,
    `user_date` datetime NOT NULL,
    PRIMARY KEY (`user_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

    categories CREATE TABLE `categories` (
    `category_id` bigint(60) NOT NULL AUTO_INCREMENT,
    `category_field` varchar(100) NOT NULL,
    PRIMARY KEY (`category_id`),
    UNIQUE KEY `category_id` (`category_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1
    Last edited by trevor2; Jan 12, 2009 at 19:43.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    tip: if you want to show us a table and its columns, use this command:
    Code:
    SHOW CREATE TABLE tablename
    and paste the results here
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

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
  •