There's a huge difference between using a spreadsheet as a database and a bona fide database.
Those stubs and manual commands you are writing are easily done with in mysql.
Code:
select sum(colA)
from my_table;
aggregates:
Code:
select colA, sum(colB)
from my_table
group by colA;
To answer the thread question, normalization is a pretty counter-intuitive.
A relation is a table, but a table is not a relation. To have a relation, you have to have the following properties:
-- Each column must hold one value and must have one datatype.
-- Each column must be unique.
-- Each row must be uniquely identifiable.
-- Order does not matter.
A simple example, using your data:
Database 1:
- - Casino ID 1 | Binnions | Las Vegas
- - Casino ID 2 | Binnions | Louisiana
Code:
create table casinos (
casino_id serial primary key,
casino_name varchar,
city varchar
unique (casino_name, city)
);
This looks okay and follows all the rules of a relation, but there's a massive data integrity issue here:
Code:
insert into casinos
values
('binnions', 'Las Vegas'),
('binions', 'Las Vegas'),
('binnion''s', 'Las Vegas')
('binnions', 'LV');
If you are writing a script that auto-fills the casino and returns the id, every mistake you make will cause duplication errors (to the human, not the machine).
To prevent this, you would actually need to split this into more tables. I suggest this:
Code:
create table casinos (
casino varchar primary key
);
insert into casinos
values ('binnions'); -- or whatever spelling you want
create table cities (
city varchar primary key
);
insert into cities
values ('Las Vegas');
create table casino_city (
casino varchar,
city varchar,
primary key (casino, city),
foreign key (casino)
references casinos (casino),
foreign key (city)
references cities (city)
);
-- this command will throw and error.
-- no values will be inserted:
insert into casino_cities
values
('binnions', 'Las Vegas'),
('binions', 'Las Vegas'),
('binnion''s', 'Las Vegas')
('binnions', 'LV');
-- this command will run:
insert into casino_cities
values ('binnions', 'Las Vegas');
Of course, you can change this around a bit. For example, you can add a serial to the casino_cities table and call it casinos instead. Perhaps call the casinos table valid_casino_names and the cities table valid_city_names, so you end up with this:
Code:
create table valid_casino_names (
casino_name varchar primary key
);
create table valid_cities (
city_name varchar primary key
);
create table casinos (
casino_id serial primary key,
casino_name varchar,
city varchar,
primary key (casino, city),
foreign key (casino_name)
references valid_casino_names (casino_name),
foreign key (city)
references cities (city)
);
insert into valid_casino_names
values ('binnions');
insert into valid_cities
values ('Las Vegas');
insert into casinos (casino_name, city)
values ('binnions', 'Las Vegas');
Last edited by daveT; 08-23-2016 at 05:09 PM.