Please critique DB schema...

mrbaseball34

I am working on building a site for listing garage sales.

Below is the initial DB schema I have come up with and I'd
like a critique. If you see anything that could be improved
upon, I'd appreciate any comments.

#
# Table structure for table 'alerts'
#
# This table allows even people that are not members of the
# site to receive alerts of upcoming sales in their zip code 
#
#

CREATE TABLE  alerts (
  alert_key bigint(3) unsigned NOT NULL auto_increment,
  email varchar(45) NOT NULL DEFAULT '' ,
  zip varchar(9) NOT NULL DEFAULT '' ,
  PRIMARY KEY (alert_key),
  INDEX alert_key (alert_key)
);


#
# Table structure for table 'category'
#
# This table holds the categories that sales can be listed under.
# Sample categories:
# Furniture, Garden/Outdoor, Hardware, Clothing/Accessories, Homewares, 
# Computers/Software, Mechanical, Electrical, Miscellaneous, White Goods 
#
# This allows users to search for particular things they want to buy.
#
# This is strictly a lookup table modified only by the administrator.

CREATE TABLE  category (
  category_key bigint(3) unsigned NOT NULL auto_increment,
  category_description varchar(40) NOT NULL DEFAULT '' ,
  category_date_created date NOT NULL DEFAULT '0000-00-00' ,
  PRIMARY KEY (category_key),
  INDEX category_key (category_key)
);


#
# Table structure for table 'comment'
#
# This table allows the user to enter comments about the
# particular sale, comments must be accompanied by a name.
# If user is logged in member, it will default to their name,
# and not be editable, if not logged i, will default to Guest.
#

CREATE TABLE  comment (
  comment_key bigint(3) unsigned NOT NULL auto_increment,
  sale_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  comment text NOT NULL DEFAULT '' ,
  name varchar(50) NOT NULL DEFAULT '' ,
  comment_date date NOT NULL DEFAULT '0000-00-00' ,
  PRIMARY KEY (comment_key),
  INDEX comemnt_key (comment_key)
);


#
# Table structure for table 'sales'
#
# This table shows detail about a particular sale.
#

CREATE TABLE  sales (
  sale_key bigint(3) unsigned NOT NULL auto_increment,
  user_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  status tinyint(3) unsigned ,
  address varchar(65) NOT NULL DEFAULT '' ,
  city varchar(40) NOT NULL DEFAULT '' ,
  state char(2) NOT NULL DEFAULT '' ,
  zip varchar(9) NOT NULL DEFAULT '' ,
  phone varchar(10) ,
  start datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
  end datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
  description text NOT NULL DEFAULT '' ,
  early_birds text ,
  category_key bigint(3) NOT NULL DEFAULT '0' ,
  date_created date NOT NULL DEFAULT '0000-00-00' ,
  featured_items text ,
  viewed bigint(3) unsigned ,
  map_image varchar(255),
  PRIMARY KEY (sale_key),
  INDEX sale_key (sale_key)
);


#
# Table structure for table 'sales_images'
#
# This table stores paths for images to items contained
# in a sale.
#

CREATE TABLE  sales_images (
  sales_images_key bigint(3) unsigned NOT NULL DEFAULT auto_increment ,
  sales_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  image_path varchar(255) NOT NULL DEFAULT '' ,
  PRIMARY KEY (sales_images_key)
);


#
# Table structure for table 'sales_category'
#
# This table holds category keys that belong to a particular
# sale.
#

CREATE TABLE  sales_category (
  sales_category_key bigint(3) unsigned NOT NULL auto_increment,
  sales_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  category_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  PRIMARY KEY (sales_category_key),
  INDEX sales_category_key (sales_category_key,sales_key)
);


#
# Table structure for table 'user'
#
# This table holds detail about a particular registered user.
#
#

CREATE TABLE  user (
  user_key bigint(3) unsigned NOT NULL auto_increment,
  user__lastname varchar(30) NOT NULL DEFAULT '' ,
  user_firstname varchar(30) NOT NULL DEFAULT '' ,
  user_email varchar(45) NOT NULL DEFAULT '' ,
  user_alerts_key bigint(3) unsigned ,
  date_created date NOT NULL DEFAULT '0000-00-00' ,
  PRIMARY KEY (user_key),
  INDEX user_key (user_key)
);


#
# Table structure for table 'user_alerts'
#
# This table stores the user's alerts.
#

CREATE TABLE  user_alerts (
  user_alerts_key bigint(3) unsigned NOT NULL auto_increment,
  user_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  alert_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  PRIMARY KEY (user_alerts_key),
  INDEX user_alerts_key (user_alerts_key)
);


#
# Table structure for table 'user_favorites'
#
# Users are allowed to select favorite sales to view in a favorites
# area.
#

CREATE TABLE  user_favorites (
  user_favorites_key bigint(3) unsigned NOT NULL auto_increment,
  user_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  sale_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  PRIMARY KEY (user_favorites_key),
  INDEX favorites_key (user_favorites_key)
);

MarkR

It is not necessary to create an index on a primary key column in MySQL, they automatically have one.
You should probably place some indexes on your foreign key columns.
You should probably be consistent about whether you're going to use singular or plural names for your tables. Personally I prefer singular.
You seem to have a lot of meaningless DEFAULTs in your database, are these needed? I find it's better to have a NOT NULL column without a DEFAULT, as it imposes more order.
What is your user_alerts_key in table user? It could be a foreign key for user_alerts, but that already has a user_key - do you really need foreign keys going in both directions? I'm unsure as to what that means.
sales_category probably wants a unique index on (sales_key, category_key)
Likewise user_favourites probably wants a unique index on (user_key, sale_key)

Be consistent about your naming of primary and foreign keys - some people call the primary key of every table "ID" and foreign keys something else.

Mark

mrbaseball34

MarkR wrote:
1. It is not necessary to create an index on a primary key column in MySQL, they automatically have one.

Please explain further what you mean here...

MarkR wrote:
2. You should probably place some indexes on your foreign key columns.

Which ones you talking about?

MarkR wrote:
3. You should probably be consistent about whether you're going to use singular or plural names for your tables. Personally I prefer singular.

Table names look ok to me.

MarkR wrote:
4. You seem to have a lot of meaningless DEFAULTs in your database, are these needed? I find it's better to have a NOT NULL column without a DEFAULT, as it imposes more order.

Not sure what you're talking about, wouldn't you then have to have all these fields ass REQUIRED in your interface?

MarkR wrote:
5. What is your user_alerts_key in table user? It could be a foreign key for user_alerts, but that already has a user_key - do you really need foreign keys going in both directions? I'm unsure as to what that means.

Yes, table user does not require the user_alerts_key.

MarkR wrote:
6. sales_category probably wants a unique index on (sales_key, category_key)

There can be more than one category for each sale.

MarkR wrote:
7. Likewise user_favourites probably wants a unique index on (user_key, sale_key)

No, favorites are many {favorites (Sales)} to one{user} but they cannot select the same sale more than once.

I also fixed a couple of typos...

laserlight

Please explain further what you mean here...

e.g.,

PRIMARY KEY (user_key),
INDEX user_key (user_key)

is redundant and can be replaced with:

PRIMARY KEY (user_key)

Table names look ok to me.

Ah, but you have a table named "alerts" and another named "category". This is okay, but to be consistent you should use either "alerts" and "categories" or "alert" and "category".

mrbaseball34

OK, I've made some changes.

#
# Table structure for table 'alerts'
#
# This table allows even people that are not members of the
# site to receive alerts of upcoming sales in their zip code
#
#

CREATE TABLE  alerts (
  alerts_key bigint(3) unsigned NOT NULL auto_increment,
  email varchar(45) NOT NULL DEFAULT '' ,
  zip varchar(9) NOT NULL DEFAULT '' ,
  PRIMARY KEY (alerts_key)
);


#
# Table structure for table 'categories'
#
# This table holds the categories that sales can be listed under.
# Sample categories:
# Furniture, Garden/Outdoor, Hardware, Clothing/Accessories, Homewares,
# Computers/Software, Mechanical, Electrical, Miscellaneous, White Goods
#
# This allows users to search for particular things they want to buy.
#

CREATE TABLE  categories (
  categories_key bigint(3) unsigned NOT NULL auto_increment,
  categories_description varchar(40) NOT NULL DEFAULT '' ,
  categories_date_created date NOT NULL DEFAULT '0000-00-00' ,
  PRIMARY KEY (categories_key)
);


#
# Table structure for table 'comments'
#
# This table allows the user to enter comments about the
# particular sale, comments must be accompanied by a name.
# If user is logged in member, it will default to their name,
# and not be editable, if not logged i, will default to Guest.
#

CREATE TABLE  comments (
  comments_key bigint(3) unsigned NOT NULL auto_increment,
  sale_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  comment text NOT NULL DEFAULT '' ,
  name varchar(50) NOT NULL DEFAULT '' ,
  comments_date date NOT NULL DEFAULT '0000-00-00' ,
  PRIMARY KEY (comments_key)
);


#
# Table structure for table 'sales'
#
# This table shows detail about a particular sale.
#

CREATE TABLE  sales (
  sale_key bigint(3) unsigned NOT NULL auto_increment,
  users_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  status tinyint(3) unsigned ,
  address varchar(65) NOT NULL DEFAULT '' ,
  city varchar(40) NOT NULL DEFAULT '' ,
  state char(2) NOT NULL DEFAULT '' ,
  zip varchar(9) NOT NULL DEFAULT '' ,
  phone varchar(10) ,
  start datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
  end datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
  description text NOT NULL DEFAULT '' ,
  early_birds text ,
  category_key bigint(3) NOT NULL DEFAULT '0' ,
  date_created date NOT NULL DEFAULT '0000-00-00' ,
  featured_items text ,
  viewed bigint(3) unsigned ,
  map_image_path varchar(255),
  PRIMARY KEY (sale_key)
);


#
# Table structure for table 'sales_images'
#
# This table stores paths for images to items contained
# in a sale.
#

CREATE TABLE  sales_images (
  sales_images_key bigint(3) unsigned NOT NULL auto_increment,
  sales_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  image_path varchar(255) NOT NULL DEFAULT '' ,
  PRIMARY KEY (sales_images_key)
);

#
# Table structure for table 'sales_categories'
#
# This table holds category keys that belong to a particular
# sale.
#

CREATE TABLE  sales_categories (
  sales_categories_key bigint(3) unsigned NOT NULL auto_increment,
  sales_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  categories_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  PRIMARY KEY (sales_categories_key),
  INDEX sales_categories_key (sales_categories_key, sales_key)
);


#
# Table structure for table 'users'
#
# This table holds detail about a particular registered user.
#
#

CREATE TABLE  users (
  users_key bigint(3) unsigned NOT NULL auto_increment,
  users_lastname varchar(30) NOT NULL DEFAULT '' ,
  users_firstname varchar(30) NOT NULL DEFAULT '' ,
  users_email varchar(45) NOT NULL DEFAULT '' ,
  date_created date NOT NULL DEFAULT '0000-00-00' ,
  PRIMARY KEY (users_key)
);


#
# Table structure for table 'users_alerts'
#
# This table stores the user's alerts.
#

CREATE TABLE  users_alerts (
  users_alerts_key bigint(3) unsigned NOT NULL auto_increment,
  users_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  alerts_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  PRIMARY KEY (users_alerts_key)
);


#
# Table structure for table 'users_favorites'
#
# Users are allowed to select favorite sales to view in a favorites
# area.
#

CREATE TABLE  users_favorites (
  users_favorites_key bigint(3) unsigned NOT NULL auto_increment,
  users_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  sale_key bigint(3) unsigned NOT NULL DEFAULT '0' ,
  PRIMARY KEY (users_favorites_key)
);

Now, I have two indexes on sales_categories table. is this really needed and do I need multiple indexes on some of the other tables?

MarkR

mrbaseball34 wrote:
There can be more than one category for each sale.

Yes, BUT there can't be more than one of the same category for a given sale.

I.e. a sale/category pair is unique in the sale_categories table.

No, favorites are many {favorites (Sales)} to one{user} but they cannot select the same sale more than once.

Likewise.

A unique index on a pair of columns ensures that the pair is unique, not that they're unique individually.

Mark