CODEWITHSUNDEEP
javasqldatabase-designstructure
thatidiotguy
thatidiotguy

Reputation: 8981

SQL Table Structure Feedback

I am not too happy with the sql table structure I have below for two reasons.

  1. I do not like that I am listing all items included in an Order (orders.itemOrderIds) in a 1020 byte varchar variable. I feel like there is a more elegant way to hold a bunch of id numbers.

  2. I am relying on extracting information about an item (basically fields) in my java code from the itemDescription variable in the item table.

Does anybody has some advice as to the best way to deal with these situations. I have read on this website that it is okay to have java code generate tables. So for instance my Java code would create a table called order_(orderId)_items that would hold all items included in the order with orderId. Is this the best way to go about this?

CREATE TABLE IF NOT EXISTS customers
(
id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
company VARCHAR(255) NOT NULL,
address1 VARCHAR(255) NOT NULL,
address2 VARCHAR(255),
city VARCHAR(255) NOT NULL,
province_state VARCHAR(255) NOT NULL,
country VARCHAR(255) NOT NULL,
zip VARCHAR(255),
telephone VARCHAR(255),
PRIMARY KEY(id)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS orders
(
id INT AUTO_INCREMENT,
customerId INT NOT NULL,
orderNum VARCHAR(255) NOT NULL,
itemOrderIds VARCHAR(1020) NOT NULL,
regName VARCHAR(255) NOT NULL,
regCompany VARCHAR(255) NOT NULL,
regEmail VARCHAR(255) NOT NULL,
orderTime DATETIME NOT NULL,
FOREIGN KEY(customerId) REFERENCES customers(id),
PRIMARY KEY(id)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS items
(
itemNum VARCHAR(255) UNIQUE,
itemName VARCHAR(255),
itemTypeId SMALLINT NOT NULL,
--itemDescription must hold all information needed to generate license keys in format
-- Field_Name1: Field_Value1, Field_Name2: Field_Value2, .....
-- Field names are platform, version, (choose one: port, voiceName)
itemDescription VARCHAR(1020),
FOREIGN KEY(itemTypeId) REFERENCES item_types(id) ON DELETE CASCADE,
PRIMARY KEY(id)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS item_types
(
id SMALLINT AUTO_INCREMENT,
itemType VARCHAR(255) UNIQUE NOT NULL,
PRIMARY KEY(id)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS item_orders
(
id INT AUTO_INCREMENT,
itemNum VARCHAR(255) NOT NULL,
licenseKey VARCHAR(255) NOT NULL,
FOREIGN KEY(itemNum) REFERENCES items(itemNum),
PRIMARY KEY(id)
) ENGINE = INNODB;

Edit:

CREATE TABLE IF NOT EXISTS Order_Items
(
orderId INT NOT NULL,
itemOrderId INT NOT NULL,
PRIMARY KEY(orderId, itemOrderId)
) ENGINE = INNODB;

Upvotes: 1

Views: 485

Answers (3)

HLGEM
HLGEM

Reputation: 96552

You want an order detail table (not a joining table). Why? Because order information is specific to a particular date. So you must store the information as it was at that date. So the order details typically include the item number, the name of the item, the price (critical to include the price, you need the price at the time the order was sold not the price 2 years later.) and any details of options such as color, number of items ordered, etc. In the order table you will also want to include details about what address it was shipped to, and the customer's name as those thigns also change over time.

I woudl also break out the customer table. Customers typically can have multiple addressses, phone number and emails and each should be a separate related table.

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21756

The best way to do with that - is to normalize all your data structure at least to 3NF.

Database Normalization

As for now - better store list of order items in separate table which also holds a reference to Orders table.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

Yes, you need an OrderItem intersection table (I usually put the parent object first in the name), rather than denormalizing all of the order IDs into a varchar field. There is no way to properly index a field like this, and it makes updates very difficult.

Read up on database normalization, these are the first first types of examples you will find.

Upvotes: 3

Related Questions