Create Tables and Fields

So my scenario is like this: I am going to have two different moodbox on the floor. I am going to be collecting data from each box separately. I also need to collect the timestamp of that each data that is going inside of the database. Andy Masking was kind enough to assist me and he quickly mocked up the schematics. I need three tables for my setup. Firstone is going to contain buttons data. The second table is going to hold the boxes data, the third one is going to hold the buttons_id from buttons table, location_id from location table and timestamp.

So the setup is like this: Button -row_id -button_name Location -row_id -location_name History -row_id -button_id -location_id -timestamp

I am using cocoamysql software to assist me to create tables and fields.

After I create my first table Button, it is time to create the fields. I need to name each field and then decide what data type it is. For an ID field, it is always an “integer”, its default is “1”, “auto-increment” is selected, and it is always “primary”. I create a field “button_name” for each different buttons. The output is “varchar” but we also have to define its maximum length in characters. “128” should be plenty long enough.

CREATE TABLE `button` (id int) DEFAULT CHARACTER SET utf8
#  create the button table 
ALTER TABLE `button` CHANGE `id` `row_id` int(11) auto_increment PRIMARY KEY
# change the default id field to row_id field and make it auto increment and 
# primary key
ALTER TABLE `button` ADD `button_name` varchar(128) NOT NULL
# adding button field whose datatype is varchar of 128 length and not null 
# in default.

I am going to have 8 different buttons associated with 8 different emotions.

Creating location table is pretty straightforward too.

CREATE TABLE `location` (id int) DEFAULT CHARACTER SET utf8
#  create the button table 
ALTER TABLE `location` CHANGE `id` `row_id` int(11) auto_increment PRIMARY KEY
# change the default id field to row_id field and make it auto increment and 
# primary key
ALTER TABLE `location` ADD `location_name` varchar(128) NOT NULL
# adding button field whose datatype is varchar of 128 length and not null 
# in default.

We will have 2 different locations at this time.

So our history table is the main table that we are going to fill with php code. Here is the table:

CREATE TABLE `history` (id int) DEFAULT CHARACTER SET utf8
#  create the button table 
ALTER TABLE `history` CHANGE `id` `row_id` int(11) auto_increment PRIMARY KEY
# change the default id field to row_id field and make it auto increment and 
# primary key
ALTER TABLE `history` ADD `location_name` varchar(128) NOT NULL
# adding button field whose datatype is varchar of 128 length and not null 
# in default.

MySQL DataTypes and SQL Commands