SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot dragonfly7's Avatar
    Join Date
    May 2003
    Location
    ontario
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    do we need foreign keys?

    We are setting up a database for users to upload procedures. There are several departments which contain several manuals which then contain several subsets, then categories, then the actual procedures. whew

    So far, we have 5 tables to handle this. We thought we should set-up foreign keys to link the tables but are getting errors. We can create the first foreign key but it won't let us create any more foreign keys using that field.

    Are we going about this the wrong way? We will need to match records later and want to be able to have drop-down menus generated from the database for depts, manuals, subsets, & categories. We have done something similar in the past but had only 2 tables to join. If anyone has any tips/suggestions please let us know.

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    are you going about this the wrong way?

    hard to tell, without seeing what you're doing

    post your CREATE TABLE statements and we'll have a look

    just remember, while the purpose of foreign keys is to ensure relational integrity, you can certainly create tables without them and handle it yourself through application logic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot dragonfly7's Avatar
    Join Date
    May 2003
    Location
    ontario
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This table would hold the titles of all manuals

    CREATE TABLE `manuals` (
    `mdid` int(5) NOT NULL default '0',
    `mid` int(5) NOT NULL auto_increment,
    `manual` varchar(75) NOT NULL default '',
    PRIMARY KEY (`mid`),
    KEY `mdid` (`mdid`)
    ) TYPE=InnoDB AUTO_INCREMENT=1 ;

    This table would hold all categories for all manuals

    CREATE TABLE `manuals_category` (
    `mid` int(5) NOT NULL default '0',
    `mcid` int(5) NOT NULL auto_increment,
    `category` varchar(150) NOT NULL default '',
    `manual` varchar(50) NOT NULL default '',
    PRIMARY KEY (`mcid`),
    KEY `mid` (`mid`)
    ) TYPE=InnoDB AUTO_INCREMENT=4 ;


    This table holds all the department names

    CREATE TABLE `manuals_dept` (
    `mdid` int(5) NOT NULL auto_increment,
    `mdept` varchar(50) NOT NULL default '',
    `manual` varchar(50) NOT NULL default '',
    PRIMARY KEY (`mdid`)
    ) TYPE=InnoDB AUTO_INCREMENT=7 ;


    This table holds the actual links to the policy files and which dept, manual, subset and category each policy belongs to

    CREATE TABLE `manuals_policies` (
    `file_name` varchar(200) NOT NULL default '',
    `file_size` varchar(200) NOT NULL default '',
    `title` varchar(200) NOT NULL default '',
    `upload_date` varchar(200) NOT NULL default '',
    `file_type` varchar(200) NOT NULL default '',
    `upload_id` int(4) NOT NULL auto_increment,
    `revisiondate` date NOT NULL default '0000-00-00',
    `mdept` varchar(50) NOT NULL default '',
    `manual` varchar(50) NOT NULL default '',
    `subset` varchar(50) default NULL,
    `category` varchar(100) NOT NULL default '',
    `policyno` varchar(10) NOT NULL default '',
    `issuedate` date NOT NULL default '0000-00-00',
    PRIMARY KEY (`upload_id`)
    ) TYPE=InnoDB AUTO_INCREMENT=13 ;

    This table holds the names of all subsets

    CREATE TABLE `manuals_subsets` (
    `mid` int(5) NOT NULL default '0',
    `msid` int(5) NOT NULL auto_increment,
    `subset` varchar(50) NOT NULL default '',
    PRIMARY KEY (`msid`),
    KEY `mid` (`mid`)
    ) TYPE=InnoDB AUTO_INCREMENT=7 ;

    Each manual can only belong to one department - but each department can have several manuals - there are several categories in each manual which can have several policies

    Subsets can only belong to one manual, but can have several categories.

    We need to generate drop down menus for dept, manual, subset and category names so that when the user uploads the actual policy files they can choose which dept, manual, subset and category each policy belongs to.

    Thanks so much for taking a look at this! We want to make sure we are on the right track before we start programming the forms.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    where are the foreign keys?

    why does the manuals_policies table have varchar fields for mdept, manual, subset, category?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot dragonfly7's Avatar
    Join Date
    May 2003
    Location
    ontario
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    where are the foreign keys?

    why does the manuals_policies table have varchar fields for mdept, manual, subset, category?
    Oops! Yes we were going to change these fields to the corresponding id field in each table. Here is the revised create table statement:

    CREATE TABLE `manuals_policies` (
    `file_name` varchar(200) NOT NULL default '',
    `file_size` varchar(200) NOT NULL default '',
    `title` varchar(200) NOT NULL default '',
    `upload_date` varchar(200) NOT NULL default '',
    `file_type` varchar(200) NOT NULL default '',
    `upload_id` int(4) NOT NULL auto_increment,
    `revisiondate` date NOT NULL default '0000-00-00',
    `mdid` int(5) NOT NULL default '0',
    `mid` int(5) NOT NULL default '0',
    `msid` int(5) default NULL,
    `mcid` int(5) NOT NULL default '0',
    `policyno` varchar(10) NOT NULL default '',
    `issuedate` date NOT NULL default '0000-00-00',
    PRIMARY KEY (`upload_id`)
    ) TYPE=InnoDB AUTO_INCREMENT=13 ;

    We were able to create a foreign key in the manuals table (mdid) which corresponds to the manuals_dept table. But when we tried to create foreign keys for the other tables but we are getting an error.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    well, i cannot comment on the design without seeing a lot of data to understand the subtle relationship differences between manual departments, manual policies, subset policies, and whatever (no, please don't post a lot of data )

    as far as the foreign keys are concerned, make sure you have an index on each of them in the table before declaring the foreign key constraint
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would help a lot to show us the printout of the errors.

    Are you just using the KEY syntax as shown in your original post to denote a foreign key giving you no referential integrity or did you update them to use the FOREIGN KEY (some_id) REFERENCES another_table(id) ON UPDATE CASCADE ... bla bla bla for referential integrity?


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
  •