newbie - Page 2

twinkiejr · #16 (**permalink**) 08-29-10, 19:57

OK thanks, I will remove some of the duplicated/unneeded information. I really appreciate the help but I still have the original question to answer. How do I get the filtering/relationships that I am wanting. Can it be done in the DBMS or can it only be done in the application being built for data entry. I would really like to do it in the DBMS, at the most basic level if possible.

Thanks again for the help.

r937 · #17 (**permalink**) 08-29-10, 20:12

Quote:

Originally Posted by twinkiejr

How do I get the filtering/relationships that I am wanting.

i'm fairly sure you can do it with foreign keys

twinkiejr · #18 (**permalink**) 08-29-10, 23:07

I have tried using foreign keysfrom the three tables talked about earlier using the fields (*)# and placing them in the inventory table but this does not seem to limit anything. I can still put any combination of the fields in the inventory table. Maybe I am doing this wrong should the foreign keys be placed from the inventory to the other tables?

Thanks for the quick responces they are really appreciated.

r937 · #19 (**permalink**) 08-30-10, 02:12

Quote:

Originally Posted by twinkiejr

Maybe I am doing this wrong should the foreign keys be placed from the inventory to the other tables?

no, that'd be wrong

you said you can still put any combination of the fields in the inventory table, so that means you haven't declared the FK from the inventory table to the year/variety table properly, because it's the year/variety table which controls the combinations

twinkiejr · #20 (**permalink**) 08-30-10, 12:47

I am a little confussed about placing a foreign key from the inventory table to the variety table. What field in the inventory table would go in the variety table as the key. Here is the inventory table (this is a non updated version of this table but is mostly correct)

Code:

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

r937 · #21 (**permalink**) 08-30-10, 13:22

Quote:

Originally Posted by twinkiejr

What field in the inventory table would go in the variety table as the key.

mine eyes, zey are bleedink!!! ze goggles, zey do nossink!!!

please, learn to format your SQL

thank you

Code:

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

what are the primary and unique keys in the year/variety table? that will tell you which keys you can reference in a FK from the inventory table

twinkiejr · #22 (**permalink**) 08-30-10, 13:34

Sorry about the formating.

In my variety table I am using variety # as primary key and variety seq is a unique key. Does this mean that I would the field in the inventory table called variety # and reference that back to the variety table variety # field.

Thanks

r937 · #23 (**permalink**) 08-30-10, 14:27

Quote:

Originally Posted by twinkiejr

Does this mean that ...

no, it doesn't

however, before we continue discussing your tables, you need to go through them and make several changes

1. eliminate the redundant identifying columns

2. change the column names to remove all spaces and special characters

twinkiejr · #24 (**permalink**) 08-30-10, 16:02

Below are hopefully the fixed tables. I have not entered the foreign keys from the type, category, and year_variety tables into the inventory table yet. I want to make sure that I am headed in the correct direction. I am going to add the table fields labeled (*)_seq to the inventory table from the above mentioned tables and add them as foriegn keys.

Thank You

Code:

CREATE TABLE 
`inventory` ( `inventory_id` int(15) NOT NULL AUTO_INCREMENT, 
`country_#` 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`) ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8

Code:

CREATE TABLE 
`type` ( `type` varchar(75) NOT NULL, 
`type_seq` int(9) NOT NULL, PRIMARY KEY (`type_seq`), UNIQUE KEY `type` (`type`) ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8

Code:

CREATE TABLE 
`category` ( `category` varchar(50) NOT NULL, `category_seq` int(6) NOT NULL, PRIMARY KEY (`category_seq`), UNIQUE KEY `category` (`category`) ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8

Code:

CREATE TABLE `year_variety` ( `year/variety` varchar(50) NOT NULL, 
`year/variety_seq` int(9) NOT NULL, PRIMARY KEY (`year/variety_seq`), KEY `year/variety` (`year/variety`) ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8

r937 · #25 (**permalink**) 08-30-10, 16:12

is there any chance -- please, you will thank me later -- that you can rename your columns so that they do not contain any spaces or special characters...

twinkiejr · #26 (**permalink**) 08-30-10, 16:38

Is this it?

Code:

CREATE TABLE 
`inventory` ( `inventoryid` int(15) NOT NULL AUTO_INCREMENT, 
`countrynum` int(9) NOT NULL, `gradenum` int(6) NOT NULL, 
`grademodifiernum` int(6) NOT NULL, `description` varchar(250) NOT NULL, 
`longdescription` varchar(250) NOT NULL, 
`catalognum` varchar(9) NOT NULL, 
`special1` varchar(12) NOT NULL, 
`special2` varchar(12) NOT NULL, 
`special3` varchar(12) NOT NULL, 
`certificationnum` int(3) NOT NULL, 
`locationnum` int(3) NOT NULL, 
`remarks` text NOT NULL, 
`statuscode` varchar(4) NOT NULL, 
`retailprice` decimal(10,2) NOT NULL, 
`usernum` int(4) NOT NULL, 
`entrydate` date NOT NULL, 
`quantity` int(4) NOT NULL, 
`costcode` varchar(12) NOT NULL, 
`purchasedate` date NOT NULL, 
`purchaseprice` decimal(10,2) NOT NULL, 
`purchaselocation` varchar(12) NOT NULL, 
`solddate` date NOT NULL, 
`soldprice` decimal(10,2) NOT NULL, 
`ordernum` int(15) NOT NULL, 
`barcodenum` int(12) NOT NULL, PRIMARY KEY (`inventoryid`), 
KEY `countrynum` (`countrynum`), 
KEY `gradenum` (`gradenum`), 
KEY `grademodifiernum` (`grademodifiernum`), 
KEY `certificationnum` (`certificationnum`), 
KEY `locationnum` (`locationnum`), 
KEY `statuscode` (`statuscode`), 
KEY `usernum` (`usernum`), 
KEY `ordernum` (`ordernum`), KEY `sold price` (`soldprice`), 
KEY `solddate` (`solddate`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Code:

CREATE TABLE 
`category` ( `category` varchar(50) NOT NULL, 
`categoryseq` int(6) NOT NULL, PRIMARY KEY (`categoryseq`), UNIQUE KEY 
`category` (`category`) ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8

Code:

CREATE TABLE 
`type` ( `type` varchar(75) NOT NULL, 
`typeseq` int(9) NOT NULL, PRIMARY KEY (`typeseq`), UNIQUE KEY `type` (`type`) ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8

Code:

CREATE TABLE 
`yearvariety` ( `yearvariety` varchar(50) NOT NULL, 
`yearvarietyseq` int(9) NOT NULL, PRIMARY KEY (`yearvarietyseq`), 
KEY `yearvariety` (`yearvariety`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

r937 · #27 (**permalink**) 08-30-10, 16:51

Quote:

Originally Posted by twinkiejr

Is this it?

that's a great improvement, and you will thank me later

now, how did you want to relate the inventory rows to the other tables?

twinkiejr · #28 (**permalink**) 08-30-10, 17:12

I seem to be getting what I want with all of the other tables accept with the three tables discussed. The whole issue is to be able to limit/filter the information going into the inventory table. I want to be able to limit the input to the inventory table for the type, category, and yearvariety tables. What I mean is that the first in order would be the category and a catagory has been picked you would be limited to only the types from the type table that had a matching catagory an then when a type is picke limit the yearvariety to only the ones that match it.

I have inserted the quote because it had some sample information in it.

Quote:

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 exist in 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.

r937 · #29 (**permalink**) 08-30-10, 18:53

i understand what you're trying to do

what you need is a table which contains the valid combinations of year, type, and category

so if 10,20,80 is valid, and 10,20,10 is not valid, you need a table which has all the valid combinations in it, and then the inventory table can reference the composite key of this table, to ensure data integrity, i.e. to ensure that only the valid combinations can be entered into the inventory table

i was under the impression that your year/variety table was intended to satisfy this requirement

twinkiejr · #30 (**permalink**) 08-30-10, 23:24

At present but probably will double before I have completed the db. I have 20 some odd categories, 240 some odd types and 6270 some odd yearvarieties. If I understand correctly I will have to combine the three to create the unique combinations. Basically if I build a table to do this will I even need the individual tables that I have now and if I do not doesn't this concept negate the idea of a relational database. If I am understanding the idea behind a db, it's to be able to store the most basic of information in tables and combine these to form a complete concise picture of the whole.

As always thanks for your help and patience.

Internet Services

Web Development

Digital Marketing

Consumer Tech