Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: newbie

  1. #1
    Join Date
    Jul 2010
    Posts
    17

    Unanswered: newbie

    I am new to sql in general, I have used access a lot in the past but as most know access helps you along quite abit. The question that I have is say I have 4 tables one being products and the other three being different but unique attributes about the product like category, type, year. How do I set up relationships or constraints so that I can not enter a year that does not match a type and a type that does not match a category into my 4th table which is my product or inventory table? Or am I over thinking this and it should only be done in the aplication that will be used for entering information into the db. Thanks in advance for your help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by twinkiejr View Post
    How do I set up relationships or constraints so that ...
    those are done with foreign keys

    declare your tables as InnoDB tables, and do some research on how to create foreign keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Posts
    17

    newbie

    I have the correct engine and have foreign keys but it is not limiting one selection based on another.

  4. #4
    Join Date
    Aug 2010
    Posts
    2
    I'm not sure what you want. Can you give examples?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, unfortunately i am still on a beta version of Microsoft® CrystalBall© and i cannot see what you've tried, you're going to have to show us
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2010
    Posts
    17

    Smile newbie

    Sorry everyone I have a table called inventory to store all of the information in one place about each product. I have three other tables one called category which has the kind of item (paper, supplies, medals). Another one called type which has a subset of the category (gold medals, silver medals; holder, folder, flips; currency, notes, script). The last table has variety which is a subset of type (certain year, certain specifics that only pertain to one item).

    category

    category category # category sort

    colonial 10 10
    continental 20 20
    us 30 30


    type

    type type # type sort category #

    SOMMER ISLANDS 10 1010 10
    NEW ENGLAND 20 1020 10
    WILLOW TREE 30 1030 10
    OAK TREE 40 1040 10
    PINE TREE 50 1050 10
    LORD BALTIMORE 60 1060 10
    SAINT PATRICK 70 1070 10
    AMERICAN PLANTATIONS 80 1080 10
    ROSA AMERICANA 90 1090 10

    variety

    year/variety year variety # type # category #
    1616 2 Large Star 10 10 10
    1616 2 Small Star 20 10 10
    1616 3 30 10 10
    1616 6 Small Portholes 40 10 10
    1616 6 Large Portholes 50 10 10
    1616 Shilling Small Sail 60 10 10
    1616 Shilling Large Sail 70 10 10
    1652 6 80 20 10
    1652 Shilling 90 20 10


    What I want is once i input the cat. # from cat. into the inventory table that i can not input a type with out it being referenced or connected to the cat. table and field/row and the same with variety. In other words using the fields labeled (*)# there would be no way that a cat. 10 type 20 variety 10 could existin the inventory table. I could only have a 10,20,80 or a 10,20,90. Again is this possible to setup using only the dbms or do I have to set this kind of functionality up with the frontend when it is created.

    All of the fields labeled with the table name and # are primary keys with foreign keys in the inventory table coresponding to the field names.

    Again thanks in advance.

    Oh and isn't most Microsoft products always in Beta
    Last edited by twinkiejr; 08-27-10 at 11:04.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do a SHOW CREATE TABLE for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2010
    Posts
    17
    CREATE TABLE `inventory` ( `inventory id` int(15) NOT NULL AUTO_INCREMENT, `type #` int(6) NOT NULL, `category #` int(6) NOT NULL, `country #` int(9) NOT NULL, `year variety #` int(9) NOT NULL, `grade #` int(6) NOT NULL, `grade modifier #` int(6) NOT NULL, `description` varchar(250) NOT NULL, `long description` varchar(250) NOT NULL, `catalog #` varchar(9) NOT NULL, `special 1` varchar(12) NOT NULL, `special 2` varchar(12) NOT NULL, `special 3` varchar(12) NOT NULL, `certification #` int(3) NOT NULL, `location #` int(3) NOT NULL, `remarks` text NOT NULL, `status code` varchar(4) NOT NULL, `retail price` decimal(10,2) NOT NULL, `user #` int(4) NOT NULL, `entry date` date NOT NULL, `quantity` int(4) NOT NULL, `cost code` varchar(12) NOT NULL, `purchase date` date NOT NULL, `purchase price` decimal(10,2) NOT NULL, `purchase location` varchar(12) NOT NULL, `sold date` date NOT NULL, `sold price` decimal(10,2) NOT NULL, `order #` int(15) NOT NULL, `barcode #` int(12) NOT NULL, PRIMARY KEY (`inventory id`), KEY `country #` (`country #`), KEY `grade #` (`grade #`), KEY `grade modifier #` (`grade modifier #`), KEY `certification #` (`certification #`), KEY `location #` (`location #`), KEY `status code` (`status code`), KEY `user #` (`user #`), KEY `order #` (`order #`), KEY `sold price` (`sold price`), KEY `sold date` (`sold date`), KEY `type #_2` (`type #`,`category #`,`year variety #`), KEY `category #` (`category #`), KEY `year variety #` (`year variety #`), CONSTRAINT `inventory_ibfk_17` FOREIGN KEY (`year variety #`) REFERENCES `year_variety` (`year/variety #`) ON UPDATE CASCADE, CONSTRAINT `inventory_ibfk_14` FOREIGN KEY (`grade #`) REFERENCES `grade` (`grade #`), CONSTRAINT `inventory_ibfk_15` FOREIGN KEY (`category #`) REFERENCES `category` (`category #`) ON UPDATE CASCADE, CONSTRAINT `inventory_ibfk_16` FOREIGN KEY (`type #`) REFERENCES `type` (`type #`) ON UPDATE CASCADE, CONSTRAINT `inventory_ibfk_2` FOREIGN KEY (`certification #`) REFERENCES `certification` (`certification #`) ON UPDATE CASCADE, CONSTRAINT `inventory_ibfk_3` FOREIGN KEY (`grade modifier #`) REFERENCES `grade_modifier` (`grade modifier #`) ON UPDATE CASCADE, CONSTRAINT `inventory_ibfk_4` FOREIGN KEY (`user #`) REFERENCES `users` (`user #`) ON UPDATE CASCADE, CONSTRAINT `inventory_ibfk_5` FOREIGN KEY (`country #`) REFERENCES `country` (`country #`) ON UPDATE CASCADE, CONSTRAINT `inventory_ibfk_6` FOREIGN KEY (`location #`) REFERENCES `location` (`location #`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    CREATE TABLE `category` ( `category` varchar(50) NOT NULL, `category #` int(6) NOT NULL, `category seq` int(6) NOT NULL, `category id` int(6) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`category #`), UNIQUE KEY `category` (`category`), UNIQUE KEY `category seq` (`category seq`), UNIQUE KEY `category id` (`category id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

    CREATE TABLE `type` ( `type` varchar(75) NOT NULL, `type #` int(6) NOT NULL, `type seq` int(9) NOT NULL, `category #` int(6) NOT NULL, `type id` int(6) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`type #`), UNIQUE KEY `type` (`type`), UNIQUE KEY `type seq` (`type seq`), UNIQUE KEY `type id` (`type id`), KEY `category #` (`category #`) ) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8

    CREATE TABLE `year_variety` ( `year/variety` varchar(50) NOT NULL, `year/variety #` int(9) NOT NULL, `type #` int(6) NOT NULL, `year/variety category seq` int(9) NOT NULL, `year/variety seq` int(9) NOT NULL, `category #` int(6) NOT NULL, `year/variety id` int(9) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`year/variety #`), UNIQUE KEY `year/variety seq` (`year/variety seq`), UNIQUE KEY `year/variety id` (`year/variety id`), KEY `type #` (`type #`), KEY `year/variety category seq` (`year/variety category seq`), KEY `year/variety` (`year/variety`), KEY `category #` (`category #`) ) ENGINE=InnoDB AUTO_INCREMENT=6307 DEFAULT CHARSET=utf8

    Thanks in advance for your help.
    Last edited by twinkiejr; 08-27-10 at 15:27.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mine eyes, zey are bleedink!!! ze goggles, zey do nossink!!!
    Code:
    CREATE TABLE `category` 
    ( `category` varchar(50) NOT NULL
    , `category #` int(6) NOT NULL
    , `category seq` int(6) NOT NULL
    , `category id` int(6) NOT NULL AUTO_INCREMENT
    , PRIMARY KEY (`category #`)
    , UNIQUE KEY `category` (`category`)
    , UNIQUE KEY `category seq` (`category seq`)
    , UNIQUE KEY `category id` (`category id`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
    ;
    CREATE TABLE `type` 
    ( `type` varchar(75) NOT NULL
    , `type #` int(6) NOT NULL
    , `type seq` int(9) NOT NULL
    , `category #` int(6) NOT NULL
    , `type id` int(6) NOT NULL AUTO_INCREMENT
    , PRIMARY KEY (`type #`)
    , UNIQUE KEY `type` (`type`)
    , UNIQUE KEY `type seq` (`type seq`)
    , UNIQUE KEY `type id` (`type id`)
    , KEY `category #` (`category #`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8
    ;
    CREATE TABLE `year_variety` 
    ( `year/variety` varchar(50) NOT NULL
    , `year/variety #` int(9) NOT NULL
    , `type #` int(6) NOT NULL
    , `year/variety category seq` int(9) NOT NULL
    , `year/variety seq` int(9) NOT NULL
    , `category #` int(6) NOT NULL
    , `year/variety id` int(9) NOT NULL AUTO_INCREMENT
    , PRIMARY KEY (`year/variety #`)
    , UNIQUE KEY `year/variety seq` (`year/variety seq`)
    , UNIQUE KEY `year/variety id` (`year/variety id`)
    , KEY `type #` (`type #`)
    , KEY `year/variety category seq` (`year/variety category seq`)
    , KEY `year/variety` (`year/variety`)
    , KEY `category #` (`category #`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=6307 DEFAULT CHARSET=utf8
    ;
    CREATE TABLE `inventory` 
    ( `inventory id` int(15) NOT NULL AUTO_INCREMENT 
    , `type #` int(6) NOT NULL
    , `category #` int(6) NOT NULL
    , `country #` int(9) NOT NULL
    , `year variety #` int(9) NOT NULL
    , `grade #` int(6) NOT NULL
    , `grade modifier #` int(6) NOT NULL
    , `description` varchar(250) NOT NULL
    , `long description` varchar(250) NOT NULL
    , `catalog #` varchar(9) NOT NULL
    , `special 1` varchar(12) NOT NULL
    , `special 2` varchar(12) NOT NULL
    , `special 3` varchar(12) NOT NULL
    , `certification #` int(3) NOT NULL
    , `location #` int(3) NOT NULL
    , `remarks` text NOT NULL
    , `status code` varchar(4) NOT NULL
    , `retail price` decimal(10,2) NOT NULL
    , `user #` int(4) NOT NULL
    , `entry date` date NOT NULL
    , `quantity` int(4) NOT NULL
    , `cost code` varchar(12) NOT NULL
    , `purchase date` date NOT NULL
    , `purchase price` decimal(10,2) NOT NULL
    , `purchase location` varchar(12) NOT NULL
    , `sold date` date NOT NULL
    , `sold price` decimal(10,2) NOT NULL
    , `order #` int(15) NOT NULL
    , `barcode #` int(12) NOT NULL
    , PRIMARY KEY (`inventory id`)
    , KEY `country #` (`country #`)
    , KEY `grade #` (`grade #`)
    , KEY `grade modifier #` (`grade modifier #`)
    , KEY `certification #` (`certification #`)
    , KEY `location #` (`location #`)
    , KEY `status code` (`status code`)
    , KEY `user #` (`user #`)
    , KEY `order #` (`order #`)
    , KEY `sold price` (`sold price`)
    , KEY `sold date` (`sold date`)
    , KEY `type #_2` (`type #`,`category #`,`year variety #`)
    , KEY `category #` (`category #`)
    , KEY `year variety #` (`year variety #`)
    , CONSTRAINT `inventory_ibfk_17` 
        FOREIGN KEY (`year variety #`) 
          REFERENCES `year_variety` (`year/variety #`) ON UPDATE CASCADE
    , CONSTRAINT `inventory_ibfk_14` 
        FOREIGN KEY (`grade #`) 
          REFERENCES `grade` (`grade #`)
    , CONSTRAINT `inventory_ibfk_15` 
        FOREIGN KEY (`category #`) 
          REFERENCES `category` (`category #`) ON UPDATE CASCADE
    , CONSTRAINT `inventory_ibfk_16` 
        FOREIGN KEY (`type #`) 
          REFERENCES `type` (`type #`) ON UPDATE CASCADE
    , CONSTRAINT `inventory_ibfk_2` 
        FOREIGN KEY (`certification #`) 
          REFERENCES `certification` (`certification #`) ON UPDATE CASCADE
    , CONSTRAINT `inventory_ibfk_3` 
        FOREIGN KEY (`grade modifier #`) 
          REFERENCES `grade_modifier` (`grade modifier #`) ON UPDATE CASCADE
    , CONSTRAINT `inventory_ibfk_4` 
        FOREIGN KEY (`user #`) 
          REFERENCES `users` (`user #`) ON UPDATE CASCADE
    , CONSTRAINT `inventory_ibfk_5` 
        FOREIGN KEY (`country #`) 
          REFERENCES `country` (`country #`) ON UPDATE CASCADE
    , CONSTRAINT `inventory_ibfk_6` 
        FOREIGN KEY (`location #`) 
          REFERENCES `location` (`location #`) ON UPDATE CASCADE 
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    ;
    could you maybe take a moment and explain what each of the columns in the category table is for

    also, could you repeat your original question please --
    How do I set up relationships or constraints so that I can not enter a year that does not match a type and a type that does not match a category into my 4th table which is my product or inventory table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2010
    Posts
    17
    The original question is that I have 4 tables one being products/inventory and the other three being different but unique attributes about the product like category, type, year. How do I set up relationships or constraints so that I can not enter a year that does not match a type and a type that does not match a category into my 4th table which is my product or inventory table? Or am I over thinking this and it should only be done in the aplication that will be used for entering information into the db.


    category columns are

    category = such as U.S., World, Colonial, etc.

    category #= this and category id ended up the same thing just havn't removed one yetThis is a unique number added to help identify the category

    category seq=this is a sequencing number for when the inventory is queried out of the inventory table

    category id=same as category #

    I hope this is what you are asking for.
    The idea is to keep someone from entering an item into the inventory that has not got the qualifying information from the three other tables

    Thanks again for any help.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by twinkiejr View Post
    ... so that I can not enter a year that does not match a type and a type that does not match a category
    this is the part that i don't understand

    how can a year match a type and a type match a category?

    is that what the year_variety table is supposed to be doing for you?

    could you please mysqldump a few rows from that table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2010
    Posts
    17

    newbie

    Here are the three tables category, type, and year_variety. As stated earlier I am a newbie and if I am going about this the wrong way please let me know.

    Thanks so much for the quick responses.



    Code:
    CREATE TABLE IF NOT EXISTS `year_variety` (
      `year/variety` varchar(50) NOT NULL,
      `year/variety #` int(9) NOT NULL,
      `type #` int(6) NOT NULL,
      `year/variety category seq` int(9) NOT NULL,
      `year/variety seq` int(9) NOT NULL,
      `category #` int(6) NOT NULL,
      `year/variety id` int(9) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`year/variety #`),
      UNIQUE KEY `year/variety seq` (`year/variety seq`),
      UNIQUE KEY `year/variety id` (`year/variety id`),
      KEY `type #` (`type #`),
      KEY `year/variety category seq` (`year/variety category seq`),
      KEY `year/variety` (`year/variety`),
      KEY `category #` (`category #`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6307 ;
    
    --
    -- Dumping data for table `year_variety`
    --
    
    INSERT INTO `year_variety` (`year/variety`, `year/variety #`, `type #`, `year/variety category seq`, `year/variety seq`, `category #`, `year/variety id`) VALUES
    ('1616 2 Pence Large Star', 10, 10, 1010, 1010, 10, 31),
    ('1616 2 Pence Small Star', 20, 10, 1020, 1020, 10, 32),
    ('1616 3 Pence', 30, 10, 1030, 1030, 10, 33),
    ('1616 6 Pence Small Portholes', 40, 10, 1040, 1040, 10, 34),
    ('1616 6 Pence Large Portholes', 50, 10, 1050, 1050, 10, 35),
    ('1616 Shilling Small Sail', 60, 10, 1060, 1060, 10, 36),
    ('1616 Shilling Large Sail', 70, 10, 1070, 1070, 10, 37),
    ('1652 6 Pence', 80, 20, 1010, 1080, 10, 38),
    ('1652 Shilling', 90, 20, 1020, 1090, 10, 39),
    ('1652 6 Pence', 100, 30, 1010, 1100, 10, 40),
    ('1652 Shilling', 110, 30, 1020, 1110, 10, 41),
    ('1662 2 Pence Sm2', 120, 40, 1010, 1120, 10, 42),
    ('1662 2 Pence Lg2', 130, 40, 1020, 1130, 10, 43),
    ('1652 3 Pence w/IN', 140, 40, 1030, 1140, 10, 44),
    ('1652 3 Pence No IN', 150, 40, 1040, 1150, 10, 45),
    ('1652 6 Pence Rev IN', 160, 40, 1050, 1160, 10, 46),
    ('1652 6 Pence Obv IN', 170, 40, 1060, 1170, 10, 47),
    ('1652 Shilling Left IN', 180, 40, 1070, 1180, 10, 48),
    ('1652 Shilling Bottom IN', 190, 40, 1080, 1190, 10, 49),
    ('1652 Shilling w/ANDO', 200, 40, 1090, 1200, 10, 50),
    ('1652 Shilling Spiny', 210, 40, 1100, 1210, 10, 51),
    ('1652 3 Pence w/Pellets', 220, 50, 1010, 1220, 10, 52),
    ('1652 3 Pence No Pellets', 230, 50, 1020, 1230, 10, 53),
    ('1652 6 Pence w/Pellets', 240, 50, 1030, 1240, 10, 54),
    ('1652 6 Pence No Pellets', 250, 50, 1040, 1250, 10, 55);
    Code:
    CREATE TABLE IF NOT EXISTS `type` (
      `type` varchar(75) NOT NULL,
      `type #` int(6) NOT NULL,
      `type seq` int(9) NOT NULL,
      `category #` int(6) NOT NULL,
      `type id` int(6) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`type #`),
      UNIQUE KEY `type` (`type`),
      UNIQUE KEY `type seq` (`type seq`),
      UNIQUE KEY `type id` (`type id`),
      KEY `category #` (`category #`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=149 ;
    
    --
    -- Dumping data for table `type`
    --
    
    INSERT INTO `type` (`type`, `type #`, `type seq`, `category #`, `type id`) VALUES
    ('SOMMER ISLANDS', 10, 1010, 10, 1),
    ('NEW ENGLAND', 20, 1020, 10, 2),
    ('WILLOW TREE', 30, 1030, 10, 3),
    ('OAK TREE', 40, 1040, 10, 4),
    ('PINE TREE', 50, 1050, 10, 5),
    ('LORD BALTIMORE', 60, 1060, 10, 6),
    ('SAINT PATRICK', 70, 1070, 10, 7),
    ('AMERICAN PLANTATIONS', 80, 1080, 10, 8),
    ('ROSA AMERICANA', 90, 1090, 10, 9),
    ('WOOD’S HIBERNIA', 100, 10100, 10, 10),
    ('VIRGINIA', 110, 10110, 10, 11),
    ('ELEPHANT TOKENS', 120, 10120, 10, 12),
    ('HIGLEY COPPERS', 130, 10130, 10, 13),
    ('VOCE POPULI', 140, 10140, 10, 14),
    ('PITT TOKENS', 150, 10150, 10, 15),
    ('RHODE ISLAND SHIP MEDALS', 160, 10160, 10, 16),
    ('CHALMERS', 170, 10170, 10, 17),
    ('FRENCH NORTH AMERICA', 180, 10180, 10, 18),
    ('NOVA CONSTELLATIO COPPERS', 190, 20190, 20, 19),
    ('IMMUNE COLUMBIA COPPERS', 200, 20200, 20, 20),
    ('MASSACHUSETTS', 210, 20210, 20, 21),
    ('CONNECTICUT COPPERS', 220, 20220, 20, 22),
    ('NEW YORK COPPERS', 230, 20230, 20, 23),
    ('MACHIN MILLS COPPERS', 240, 20240, 20, 24),
    ('NOVA EBORAC COPPERS', 250, 20250, 20, 25);
    Code:
    CREATE TABLE IF NOT EXISTS `category` (
      `category` varchar(50) NOT NULL,
      `category #` int(6) NOT NULL,
      `category seq` int(6) NOT NULL,
      `category id` int(6) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`category #`),
      UNIQUE KEY `category` (`category`),
      UNIQUE KEY `category seq` (`category seq`),
      UNIQUE KEY `category id` (`category id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
    
    --
    -- Dumping data for table `category`
    --
    
    INSERT INTO `category` (`category`, `category #`, `category seq`, `category id`) VALUES
    ('colonial coins', 10, 10, 1),
    ('continental coins', 20, 20, 2),
    ('u.s. coins', 30, 30, 3);

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow, those are some weird collections of data

    it appears to me that you have double the number of identifying columns as needed in each table

    for example, i really don't see how you would need both a type number and a type id

    this is what i was hoping you would explain when i asked you about the columns in the category table

    as for your year/variety table, seeing the data sure helps

    i'm going to guess a bit and say that you should ~not~ have category columns in the year/variety table, since each year/variety belongs to only one type and each type belongs to only one category
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jul 2010
    Posts
    17

    newbie

    OK I am still learning. You are right about the year_variety table each is unique to the previous table. As for the duplicate numbers or extra columns I thought originally that I had to have an autogenerated key and then I need room between category numbers for future categories to fit in the sequence without having to renumber the whole table to accomodate a new entry. Even though there is duplicate or extra information hopefully this will help you understand what I am trying to do.

    Thanks.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by twinkiejr View Post
    I thought originally that I had to have an autogenerated key
    you don't

    Quote Originally Posted by twinkiejr View Post
    and then I need room between category numbers for future categories to fit in the sequence without having to renumber the whole table to accomodate a new entry
    you don't have to do this either, if you are using a sequence column for that purpose

    in any case you certainly don't need both a number and an id

    in fact for the category and type tables, i might not even use either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •