Guys, how could I make my table layouts better?

[Namtip]

I've looked on Google but I can't find any real life examples of database layouts. I've looked at normalization, I think I've got the jist of it, so I've had a crack at creating my own database.

 

The site will have two types of users.

 

Seller: has 1 page(his own internet shop) that sells a number of items from that same page. The user will also have a his own profile and information bit.

 

Buyer: does not need a profile, he just has his own contact information stored so he can buy stuff.

 

$query = 'CREATE TABLE IF NOT EXISTS user (
			name_id						INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
			name						VARCHAR(50)	NOT NULL,
			password    				CHAR(41)    NOT NULL,
			admin_level TINYINT UNSIGNED NOT NULL DEFAULT 0
	PRIMARY KEY (name_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

// create the user information table
$query = 'CREATE TABLE IF NOT EXISTS contact (
			name_id						INTEGER UNSIGNED NOT NULL,
			first_name  				VARCHAR(30) NOT NULL,
			last_name   				VARCHAR(30) NOT NULL,
			email						VARCHAR(50),
			address						VARCHAR(50) NOT NULL,
			city        				VARCHAR(30),
			county       				CHAR(25),
			post	     				VARCHAR(9),
			home						VARCHAR(13),
			mobile						VARCHAR(13),

	FOREIGN KEY (name_id) REFERENCES user(name_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

$query = 'CREATE TABLE IF NOT EXISTS profile (
			name_id					INTEGER UNSIGNED NOT NULL,
			bio						VARCHAR(500),
			info					VARCHAR(500),

	FOREIGN KEY (name_id) REFERENCES user(name_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

//create the images table
$query = 'CREATE TABLE images (
        item_id       		INTEGER      	NOT NULL AUTO_INCREMENT,
        item_title  		VARCHAR(255) 	NOT NULL,
        item_medium 		VARCHAR(255) 	NOT NULL,
        item_dimensions 	VARCHAR(255) 	NOT NULL DEFAULT "",
        item_date     		DATE         	NOT NULL,
	item_price			NUMERIC(9,2) 	NOT NULL,
	item_quantity		INTEGER 		NOT NULL,

        PRIMARY KEY (image_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));


$query = 'CREATE TABLE IF NOT EXISTS transactions (
        item_id       		INTEGER      	NOT NULL,
        name_id  			INTEGER      	NOT NULL,
        name_id 			INTEGER			NOT NULL,
        credit_card_type 	VARCHAR(255) 	NOT NULL DEFAULT "",
        card_no     		INTEGER         NOT NULL,
	name_on_card		VARCHAR(50)		NOT NULL,
	datetime			DATETIME		NOT NULL,
	image_quantity		INTEGER 		NOT NULL,

        FOREIGN KEY (image_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

 

Is this a good database? How could I make it better? I know the values are abit off. Is it possible to store the two name_ids (buyer and seller) in the tranaction table like that? Do you have to store credit card details for transactions? If there are any sites of real examples of business databases I'd love to have a look if you could forward them. I'm not too concerned about indexes at this point.

 

[Namtip]

Is this a bad post?  :-\

[kickstart]

Hi

 

Several of your tables have no primary keys.

 

Your transactions table has name_id twice.

 

You have a field for credit card type on the transactions table which is a varchar(50). I would suggest your split off credit card type to a separate table and just use the id from that table in the transactions table (as I assume that credit card type will be the same for many rows).

 

Image dimensions I would split off to 2 separate columns, one for width and one for height to allow them to be easily searched.

 

Until fairly recent versions of MySQL the max length of a varchar field was 255, and a few of yours are longer than that.

 

Hope that helps.

 

All the best

 

Keith

[elmas156]

Hey Keith (kickstart)

 

You said that until recent versions of MySQL, a varchar field was limited to 255... what field type do you suggest if someone needs a field with a large amount of characters, such as a message or in this case, a bio?  Thanks.

[kickstart]

Hi

 

Probably a TEXT field, but depends on whether you need any compatibility with older versions of MySQL.

 

All the best

 

Keith

[Namtip]

Thanks for the input I've replaced one of my name_ids with name, I will also incorporate the things you said about image dimensions and a seperate table for credit car type.

 

Hi

 

Probably a TEXT field, but depends on whether you need any compatibility with older versions of MySQL.

 

All the best

 

Keith

 

"VARCHAR is now up to 65K in v5 -- almost no need to ever use TEXT, which is *very* evil... ...TEXT is evil because it silently forces mysql to make a temporary table for group by / sorting operations... amongst other evil things." - Fenway - http://www.phpfreaks.com/forums/index.php?topic=189579.0

 

Not trying to be a smart ass, just trying to figer out what not to use.

 

My database would not be using an older sql database. So I guess Varchar would be best?

[kickstart]

Hi

 

I would agree to use varchar, just pointed it out as something to be aware of if you might need to use it with an older MySQL install.

 

All the best

 

Keith