Step 2: Convert weak entities to tables : PHP

For each weak entity in the model-there are three: inventory, order, and item-translate the entity directly to a CREATE TABLE statement as in Step 1.
Include all attributes as in Step 1.
Include as attributes the primary key attributes of the owning entity; that is, the entity the weak entity is related to. These attributes are in the table and are also included as part of the primary key of the weak entity.

For example, for the inventory entity, create the following:

CREATE TABLE inventory (
  wine_id int(5) DEFAULT '0' NOT NULL,
  inventory_id int(3) NOT NULL,
  on_hand int(5) NOT NULL,
  cost float(5,2) NOT NULL,
  case_cost float(5,2) NOT NULL,
  dateadded timestamp(12) DEFAULT NULL,
  PRIMARY KEY (wine_id,inventory_id)
);

The wine_id is included from the wine table and forms part of the PRIMARY KEY definition. All attributes can't be NULL in this inventory table, so you'll note liberal use of NOT NULL. The dateadded attribute has a DEFAULT NULL, which if no value is inserted, is automatically filled with the current date and time.

A similar approach is taken with orders, in which cust_id is included from the customer table as an attribute and as part of the PRIMARY KEY definition:

CREATE TABLE orders (
  cust_id int(5) DEFAULT '0' NOT NULL,
  order_id int(5) DEFAULT '0' NOT NULL,
  date timestamp(12),
  discount float(3,1) DEFAULT '0.0',
  delivery float(4,2) DEFAULT '0.00',
  note varchar(120),
  PRIMARY KEY (cust_id,order_no)
);

The items table is slightly more complex, but made easier because orders has already been defined. The items table includes the PRIMARY KEY attributes of the entity it is related to (that is, orders). Because the PRIMARY KEY of orders is already resolved, the resolution is as follows:

CREATE TABLE items (
  cust_id int(5) DEFAULT '0' NOT NULL,
  order_id int(5) DEFAULT '0' NOT NULL,
  item_id int(3) DEFAULT '1' NOT NULL,
  qty int(3),
  price float(5,2),
  date timestamp(12),
  PRIMARY KEY (cust_id,order_no,item_id)
);