Database design help

Question

CREATE TABLE cards (
  id INTEGER(1) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL DEFAULT 'NULL',
  expire DATETIME NOT NULL DEFAULT 'NULL',
  PRIMARY KEY (id)
);


CREATE TABLE services (
  id INTEGER AUTO_INCREMENT DEFAULT NULL,
  name VARCHAR(50) NOT NULL DEFAULT 'NULL',
  PRIMARY KEY (id)
);

CREATE TABLE shops (
  id INTEGER AUTO_INCREMENT DEFAULT NULL,
  name VARCHAR(50) NOT NULL DEFAULT 'NULL',
  PRIMARY KEY (id)
);

CREATE TABLE card_transactions (
  id INTEGER AUTO_INCREMENT DEFAULT NULL,
  id_card INTEGER(1) NOT NULL,
  funds DOUBLE(6,2) NOT NULL,
  timeCET DATETIME NOT NULL,
  id_shops INTEGER DEFAULT NULL,
  id_services INTEGER DEFAULT NULL,
  id_payment_systems INTEGER DEFAULT NULL,
  user ENUM('y','n') NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE payment_systems (
  id INTEGER AUTO_INCREMENT DEFAULT NULL,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE shop_sales (
  id INTEGER AUTO_INCREMENT DEFAULT NULL,
  id_shops INTEGER DEFAULT NULL,
  delivery_price DOUBLE NOT NULL,
  sales price DOUBLE NOT NULL,
  PRIMARY KEY (id)
);

Sorry i have an image but can't post it. So i post the code.
Is this a good db design? Would it be better if replace the last four columns from card_transactions table with for example reference_id and put them in new table called reference?

Answer 1

I can only guess. Possible problems include

• No foreign key constraints.
• No unique constraints.
• No check constraints.
• Using floating point data types for money.
• No default timestamps for DATETIME columns.