Forum Moderators: coopster
I want to make sure when I load data for record one, it all does to record one.
This is a sports memrobalia db and accuracy is key.
Appreciate your help
PRODUCTS
Product_id (PK use auto increment)
Description
Price
Sport_category_id (FK from above table)
This assumes that each product can have only 1 category.
Each primary key has a descriptive name. I recommend using the auto increment so that no errors can take place. The database assigns a s value so there is no room for human errors to filter in.
Not sure what posting a link to another thread I started but didn't answer my question means.
I guess the question is does the foreign key "connect" each record regardless of the table? Another words each entry "ID" should be represntative in each table as a foreign key?
Hehehe, I did not read who posted the first thread. I was not taking the mickey :)
Do you follow the table representation that I posted above?
>>I guess the question is does the foreign key "connect" each
>> record regardless of the table?
That sounds about right.
SPORT_CATEGORYS
Sport_category_id (PK use auto increment)
Sport
Description
PRODUCTS
Product_id (PK use auto increment)
Description
Price
Sport_category_id (FK from above table)
So in SPORTS_CATEGORIES we have records
Sports_category_id = 1,2,3
Sport = football, golf, tennis
Desc = use your imagination
And in PRODUCTS we have
Product_id = 1,2,3,4
Descriptions = a fooball poster, a golf poster, a tennis poster, a football
Price = £10, £20, £30, £40
Sport_catgory_id = 1,2,3,1
See how there are two instances of the number one in the products table in sports_category_id.
So in this case each product can only have one category. If you want more than one category you will need a linking table but to be honest until the light comes on on this one you may struggle. Keep with it, once you get it you will marvel how simple it is.
HTH a bit
Foreign key clause
A foreign key references a primary key or a unique key in the same or another table. A foreign key assignment indicates that referential integrity is to be maintained according to the specified referential constraints. You define a foreign key with the FOREIGN KEY clause in the CREATE TABLE or ALTER TABLE statement.
Defining referential constraints
Referential integrity is imposed by adding referential constraints to table and column definitions. Once referential constraints are defined to the database manager, changes to the data within the tables and columns is checked against the defined constraint. Completion of the requested action depends on the result of the constraint checking.
Procedure
Referential constraints are established with the FOREIGN KEY clause, and the REFERENCES clause in the CREATE TABLE or ALTER TABLE statements. There are effects from a referential constraint on a typed table or to a parent table that is a typed table that you should consider before creating a referential constraint.
The identification of foreign keys enforces constraints on the values within the rows of a table or between the rows of two tables. The database manager checks the constraints specified in a table definition and maintains the relationships accordingly. The goal is to maintain integrity whenever one database object references another.
For example, primary and foreign keys each have a department number column. For the EMPLOYEE table, the column name is WORKDEPT, and for the DEPARTMENT table, the name is DEPTNO. The relationship between these two tables is defined by the following constraints:
The SQL statement defining the parent table, DEPARTMENT, is:
CREATE TABLE DEPARTMENT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(29) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT CHAR(3) NOT NULL,
LOCATION CHAR(16),
PRIMARY KEY (DEPTNO))
IN RESOURCE
CREATE TABLE EMPLOYEE
(EMPNO CHAR(6) NOT NULL PRIMARY KEY,
FIRSTNME VARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3),
PHONENO CHAR(4),
PHOTO BLOB(10m) NOT NULL,
FOREIGN KEY DEPT (WORKDEPT)
REFERENCES DEPARTMENT ON DELETE NO ACTION)
IN RESOURCE
The delete rule for the referential constraint in the employee table is NO ACTION, which means that a department cannot be deleted from the DEPARTMENT table if there are any employees in that department.
reference: h**p://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/t0004980.htm
I have elimated/combined some tables so now I have just three. Two are data and one is for a image. Now I am reading the image can be a problem too. UGH.
I am using phpmyadmin, they don't have the foreign key code. They have primary, index.
Is there a way I can export what I have and post it here to make sure I am not totally messed up.
MySQL doesn't have real foreign key support yet. With most other databases, you can specify that a category can not be deleted if there are still items in it- a very handy way to avoid catastrophes. Have you read the article in phpbuilder I recommended?
If you are still banging your head, post the code.
Didn't follow what you said about the images, though.
Here we go:
# phpMyAdmin MySQL-Dump
# version 2.5.1
# Server version: 3.23.58
# PHP Version: 4.3.3
# Database : `memorabilia`
# Table structure for table `Card`
CREATE TABLE `Card` (
`card ID` tinyint(4) NOT NULL auto_increment,
`Date` year(4) NOT NULL default '0000',
`Manufacturer` varchar(15) NOT NULL default '',
`Series` varchar(15) NOT NULL default '',
`Condition` varchar(10) NOT NULL default '',
`Current value` varchar(5) NOT NULL default '$0.00',
`Price paid` varchar(5) NOT NULL default '$0.00',
`Price sold` varchar(5) NOT NULL default '$0.00',
`Comments` text,
PRIMARY KEY (`card ID`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
# Dumping data for table `Card`
INSERT INTO `Card` VALUES (1, '2003', 'Topps', 'All American', 'Mint', '$0.00', '$0.00', '$0.00', 'This is a test');
# Table structure for table `photo`
CREATE TABLE `photo` (
`photo id` tinyint(4) NOT NULL auto_increment,
`Photo` longblob,
`Card ID` tinyint(4) NOT NULL default '0',
UNIQUE KEY `ID` (`photo id`),
KEY `Card ID` (`Card ID`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
# Dumping data for table `photo`
INSERT INTO `photo` VALUES (1, 'ÿØÿà\...etc
Crazy stuff continues but I deleted it
# Table structure for table `player`
CREATE TABLE `player` (
`player ID` tinyint(4) NOT NULL auto_increment,
`First` char(12) NOT NULL default '',
`Last` char(20) NOT NULL default '',
`Suffix` char(3) default NULL,
`Team` char(40) NOT NULL default '',
`Sport` char(12) NOT NULL default '',
`Card ID` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`player ID`),
UNIQUE KEY `ID` (`player ID`),
KEY `Card ID` (`Card ID`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
# Dumping data for table `player`
INSERT INTO `player` VALUES (1, 'Wayne', 'Chrebett', NULL, 'New York Jets', 'Football', 1);
[edited by: jatar_k at 6:55 pm (utc) on Dec. 22, 2003]
[edit reason] trimmed down extra code [/edit]
First of all, unless you improve your naming conventions, you'll inevitably get lost with bigger projects. I suggest using underscores instead of spaces (avoids the need for single quotes in field names), and having all lowercase names. When a field is a primary key, make it start with "pk_"; for a foreign key, "fk_" followed by the name of the primary key it is referencing. e.g. pk_card_id and fk_card_id.
As far as the confusion, you're not normalizing properly. Can there be two cards for one player? Then Card should have a fk_player_id field. Right now you would have to enter the same player twice to reference 2 cards, which duplicates data.
There will be more complications for a full-fledged system. If you wanted to search all the cards by a certain manufacturer, you could use the MySQL ENUM, or have another table listing all manufacturers and a foreign key in Card, fk_manufacturer_id. If you want to search by player - not just while they were playing for a specific team - you'll need to split that up in two tables too.
Excuse the novice question but what will fk_player_id field in card do for me? Does that mean that I enter "1" in that field and the DB will know using the players table who "1" is?
I don't see this growing, other than images. And if it does hopefully I will understand it better.
Searching wise, I did have teams, sports and player name as seperate tables. I also had conditon, manufacturer and series as 1 table. But combined everything to try to make it easier and prevent errors. For example I didn't want to make a Wayne Chrebett card in poor condition because I forgot what ID he was. Hope that makes sense.
Thanks a lot for your help. This is more informative than the book I have. How did you become an expert?
Once you have the data normalized, you can query it:
SELECT *
FROM Card , Player
WHERE Card.fk_player_id = Player.pk_player_id
Thanks again, let me know what you think
# Table structure for table `comments`
CREATE TABLE `comments` (
`pk_comments_id` tinyint(4) NOT NULL auto_increment,
`fk_player_id ` tinyint(4) NOT NULL default '0',
`comments` text,
`fk_item_id ` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`pk_comments_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
# Table structure for table `condition`
CREATE TABLE `condition` (
`fk_item_id ` tinyint(4) NOT NULL default '0',
`pk_condition_id` tinyint(4) NOT NULL auto_increment,
`condition` char(10) NOT NULL default '',
PRIMARY KEY (`pk_condition_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
# Table structure for table `manufacturer`
CREATE TABLE `manufacturer` (
`pk_manufacturer_id` tinyint(4) NOT NULL auto_increment,
`fk_item_id ` tinyint(4) NOT NULL default '0',
`fk_player_id ` tinyint(4) NOT NULL default '0',
`manufacturer` char(20) NOT NULL default '',
`series` char(15) NOT NULL default '',
`date` year(4) NOT NULL default '0000',
PRIMARY KEY (`pk_manufacturer_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
# Table structure for table `photo`
CREATE TABLE `photo` (
`pk_photo_id` tinyint(4) NOT NULL auto_increment,
`fk_item_id` tinyint(4) NOT NULL default '0',
`fk_player_id ` tinyint(4) NOT NULL default '0',
`fk_sport_id` tinyint(4) NOT NULL default '0',
`photo` longblob,
PRIMARY KEY (`pk_photo_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
# Table structure for table `player`
CREATE TABLE `player` (
`pk_player_id` tinyint(4) NOT NULL auto_increment,
`fk_item_id ` tinyint(4) NOT NULL default '0',
`first` char(15) NOT NULL default '',
`last` char(15) NOT NULL default '',
`suffix` char(4) default NULL,
PRIMARY KEY (`pk_player_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
# Table structure for table `sport`
CREATE TABLE `sport` (
`pk_sport_id` tinyint(4) NOT NULL auto_increment,
`fk_item_id ` tinyint(4) NOT NULL default '0',
`fk_player_id` tinyint(4) NOT NULL default '0',
`sport` char(1) NOT NULL default '',
PRIMARY KEY (`pk_sport_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
# Table structure for table `team`
CREATE TABLE `team` (
`pk_team_id` tinyint(4) NOT NULL auto_increment,
`fk_player_id ` tinyint(4) NOT NULL default '0',
` fk_item_id` tinyint(4) NOT NULL default '0',
`team` char(1) NOT NULL default '',
PRIMARY KEY (`pk_team_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
# Table structure for table `value`
CREATE TABLE `value` (
`pk_value_id` tinyint(4) NOT NULL auto_increment,
`fk_sport_id` tinyint(4) NOT NULL default '0',
`fk_item_id ` tinyint(4) NOT NULL default '0',
`fk_player_id ` tinyint(4) NOT NULL default '0',
`current_value` char(1) NOT NULL default '0',
`price_paid` char(1) default '0',
`price_sold` char(1) default '0',
PRIMARY KEY (`pk_value_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
[edited by: jatar_k at 4:29 pm (utc) on Dec. 23, 2003]
[edit reason] trimmed down code [/edit]
Are you using a modelling tool?
I know a dba is not in my future.
Thanks again for the help
