Am I close?

first db attempt

[Acternaweb]

OK, I think I may have it, but would like some help and oppinions. Am I close?

# phpMyAdmin MySQL-Dump
# version 2.5.1
# [phpmyadmin.net...] (download page)
#
# Host: localhost
# Generation Time: Dec 25, 2003 at 10:37 AM
# Server version: 3.23.58
# PHP Version: 4.3.3
# Database : `memorabilia`
# --------------------------------------------------------

#
# Table structure for table `comments`
#
# Creation: Dec 23, 2003 at 06:07 AM
# Last update: Dec 23, 2003 at 06:07 AM
#

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 ;

#
# Dumping data for table `comments`
#

# --------------------------------------------------------

#
# Table structure for table `condition`
#
# Creation: Dec 23, 2003 at 05:54 AM
# Last update: Dec 25, 2003 at 10:33 AM
#

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=4 ;

#
# Dumping data for table `condition`
#

INSERT INTO `condition` VALUES (1, 1, 'good');
INSERT INTO `condition` VALUES (2, 2, 'mint');
INSERT INTO `condition` VALUES (3, 3, 'poor');
# --------------------------------------------------------

#
# Table structure for table `item`
#
# Creation: Dec 23, 2003 at 08:55 AM
# Last update: Dec 23, 2003 at 08:55 AM
#

CREATE TABLE `item` (
`pk_item_id` tinyint(4) NOT NULL auto_increment,
PRIMARY KEY (`pk_item_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `item`
#

# --------------------------------------------------------

#
# Table structure for table `manufacturer`
#
# Creation: Dec 23, 2003 at 06:22 AM
# Last update: Dec 23, 2003 at 06:22 AM
#

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 ;

#
# Dumping data for table `manufacturer`
#

# --------------------------------------------------------

#
# Table structure for table `photo`
#
# Creation: Dec 23, 2003 at 06:03 AM
# Last update: Dec 23, 2003 at 06:03 AM
#

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 ;

#
# Dumping data for table `photo`
#

# --------------------------------------------------------

#
# Table structure for table `player`
#
# Creation: Dec 23, 2003 at 05:58 AM
# Last update: Dec 25, 2003 at 10:32 AM
#

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=4 ;

#
# Dumping data for table `player`
#

INSERT INTO `player` VALUES (1, 1, 'Wayne', 'Chrebett', NULL);
INSERT INTO `player` VALUES (2, 2, 'Mike', 'Piazza', NULL);
INSERT INTO `player` VALUES (3, 3, 'Chad', 'Pennington', NULL);
# --------------------------------------------------------

#
# Table structure for table `sport`
#
# Creation: Dec 25, 2003 at 10:36 AM
# Last update: Dec 25, 2003 at 10:36 AM
#

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(10) NOT NULL default '',
PRIMARY KEY (`pk_sport_id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

#
# Dumping data for table `sport`
#

INSERT INTO `sport` VALUES (1, 1, 1, 'football');
INSERT INTO `sport` VALUES (2, 2, 2, 'baseball');
INSERT INTO `sport` VALUES (3, 3, 3, 'football');
# --------------------------------------------------------

#
# Table structure for table `team`
#
# Creation: Dec 25, 2003 at 10:34 AM
# Last update: Dec 25, 2003 at 10:35 AM
#

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(15) NOT NULL default '',
PRIMARY KEY (`pk_team_id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

#
# Dumping data for table `team`
#

INSERT INTO `team` VALUES (1, 1, 1, 'New York Jets');
INSERT INTO `team` VALUES (2, 2, 2, 'New York Mets');
INSERT INTO `team` VALUES (3, 3, 3, 'New York Jets');
# --------------------------------------------------------

#
# Table structure for table `value`
#
# Creation: Dec 23, 2003 at 06:06 AM
# Last update: Dec 23, 2003 at 06:06 AM
#

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 ;

#
# Dumping data for table `value`
#

[Elijah]

If you could please explain what you are trying to do we could help you better ;)

[ergophobe]

Acternaweb,

Please consult Posting Code - Guidelines for Members [webmasterworld.com]

You'll get better answers if you have more focussed questions
Tom

[Acternaweb]

What I am trying to go, hmm good question. Basically I have a lot of sport trading cards and memorabilia that I want to organize and display. I was told that mysql would be a good platform to store the info and display it, but nothing for sale. I have several tables and fields to capture the content I want, plus plan on having images.

It was suggested that I have more than one table, but not sure if that is right. If I have a massive table is it easier to break it down till smaller tables?

[jimbeetle]

If it's just for display and not sale what might work for you instead of reinventing the wheel is one of the many photo gallery scripts available. They come in a variety of flavors and variety of prices, many freeware or shareware.

Do a Google search for 'photo gallery script' or go to your favorite script site, such as hotscripts, and search for 'photo gallery.'

Pre-made scripts can save a lot of time and effort and you can use what you've already learned about php to tweak a script to suit your needs.

[Acternaweb]

A 'photo gallery' isn't really what I want and doesn't have the fields that I would need to capture let alone the searches. I looked at many before. I also looked at shopping carts but they seem all to be commerece based. I wish there was a canned on-line catalog script but I wasnt able to find one

[Elijah]

I don't think I would use so many tables.
As you see you are having a field called: fk_item_id on every table.
I think it would be a lot eaiser to use one big table instead of several smaller ones. It seems like it would be very hard to update when using a bunch of smaller tables.

You could use something similar to this:

# Table structure for table `memorabilia`
#
# Creation: Dec 26, 2003 at 09:06 AM
# Last update: Dec 26, 2003 at 09:06 AM
#
CREATE TABLE `memorabilia` (
 `id` tinyint(4) NOT NULL auto_increment,
 `item` varchar(255) NOT NULL default '',
 `condition` varchar(10) NOT NULL default '',
 `comments` text,
 `manufacturer` varchar(20) NOT NULL default '',
 `series` varchar(15) NOT NULL default '',
 `date` year(4) NOT NULL default '0000',
 `photo` longblob,
 `name` varchar(255) NOT NULL default '',
 `sport` varchar(10) NOT NULL default '',
 `team` varchar(15) NOT NULL default '',
 `current_value` decimal(4,2) NOT NULL default '0.00',
 `price_paid` decimal(4,2) default '0.00',
 `price_sold` decimal(4,2) default '0.00',
 PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
#
# Dumping data for table `memorabilia`
#
INSERT INTO `memorabilia` VALUES (1, 'Baseball Card', 'Good', 'Keep this card. Might be worth a lot someday.', 'Donruss', '740', '1992', '', 'Sammy Sosa', 'Baseball', 'White Sox', '1.01', '0.50', '1.50');

[travelbuff]

Elijah wrote:

I don't think I would use so many tables.
As you see you are having a field called: fk_item_id on every table.
I think it would be a lot eaiser to use one big table instead of several smaller ones. It seems like it would be very hard to update when using a bunch of smaller tables.

I don't agree. Even if you are not going to have a huge amount of records, from a design standpoint I think that a newbie, such as myself and acternaweb, should follow the rules as much as possible. When we get to be better coders we can cut corners.

check out [devshed.com...] to learn why normalization is important.

[Acternaweb]

Thanks.. like everything else, there seems to be different points of view. I have a book, php and mysql web development (it is dull as anything) and he recommends many tables to do queries. But when you setup the shopping card there are not many tables. However there aren't many queries or cross table searches.

Going to try to find a book that has an example of what I a want...