Define a Table
Every product will have different features available, particularly with respect to datatypes and the level of constraints you can set during a create table statement. However, the standard includes much of what you need on a day to day basis. As an example, lets create three tables, orders, items, and customers.
Some basic facts about the above, there are some arbitrary limits in the definition, states have a two character code, zip codes are five digits,names are limited by length, as are street addresses. In the case of the order, there is a default of 1 for quantity, this makes some sense. There are not null prescriptions, the default behavior in sql is to allow columns to be unspecified. The reasoning behind this is twofold, either that represents unknown information, we’re sure Tony James has a gender, but unclear which it was, so a gender field would allow a null to represent a missing value, or represents an empty or not-applicable set. The danger in the first is that it’s difficult to reason about the proper outcome, the danger in the second is that it’s a violation of first normal form.
Insert data into a table
So now we have some tables, lets populate one with a few values. First, let’s create two customers:
INSERT statement will give an output that shows the number of rows affected (should be two here). Quick takeaway,
since each column was set not null by definition, we need to add to each of them. The
list of column names before values is not strictly needed, if ommitted, you will need to know what order
the create table listed the columns in. If nothing else, it serves as very clear documentation of what you intend to do.
It is possible to add columns later to a table, and code that generates inserts as above with explicit
column names will be able to weather a reorganization, but code that counted on the implicit ordering of columns will be
more likely to need adjustment. Let the database system worry about column ordering.
With any luck, you’ll see a table like this one when you confirm you inserted the data:
So now that Victor and I have accounts at the store, we should be lured in by attractive pricing on fabulous items. Let’s create a few baubles to hawk:
At this point, I realize our items table is missing a short name, only has a fulltext description. If all we sell are apples and tomatoes, then the description is the short name. A cursory glance above shows this is not necessarily the case.
Alter Table to improve design
We can add two possible fixes. We can drop the items table and create a new one, or we can just add a new column. Because
of the foreign key references to
item_id in the orders table, we’ll just add a name column:
So that wasn’t too bad. I haven’t tested that in mysql, it might be different. Basic ideas, you can add columns apart from
create table statements using alter table statements, you can add constraints (not null column, unique column) using alter table.
And most importantly,
UPDATE allows you to change values in a row. Here I used the item_id because I only had two rows, for larger tables you would likely want an update to follow a specific select option.
Update data in the table
Let’s remove just one more bit of redundancy, right now the item name is in two places (name, description). Let’s fix the descriptions by cutting the part before that dash:
So now, after we altered items to have three columns, we can insert using the three column format