SQL modelling, things to consider
Keep in mind if you can figure out a unique-key (can be composite) in a table (atleast one).
Try to see the query pattern of your application. In many cases that will determine how you model your schema. This will often have huge impact in your performance of queries (application)
Following notes is based on udacity ud197 course lecture #4. There are additional material also. But this can serve you as a quick refresher.
Normalization rules
These rules are there for guidance and there exist either due to
- Efficiently storing data
- make use of SQL infrastructure (query) and join effeciently
Based on rule #1, you can’t have the sparse data.
Rule #2 is there so that you can create index in a table. you can have multiple column as “unique key” also.
Rules for normalized tables:
1. Every row has the same number of columns.
In practice, the database system won’t let us literally have different numbers of columns in different rows. But if we have columns that are sometimes empty (null) and sometimes not, or if we stuff multiple values into a single field, we’re bending this rule.
The example to keep in mind here is the diet table from the zoo database. Instead of trying to stuff multiple foods for a species into a single row about that species, we separate them out. This makes it much easier to do aggregations and comparisons.
2. There is a unique key and everything in a row says something about the key.
The key may be one column or more than one. It may even be the whole row, as in the diet table. But we don’t have duplicate rows in a table.
More importantly, if we are storing non-unique facts — such as people’s names — we distinguish them using a unique identifier such as a serial number. This makes sure that we don’t combine two people’s grades or parking tickets just because they have the same name.
3. Facts that don’t relate to the key belong in different tables.
The example here was the items table, which had items, their locations, and the location’s street addresses in it. The address isn’t a fact about the item; it’s a fact about the location. Moving it to a separate table saves space and reduces ambiguity, and we can always reconstitute the original table using a join.
4. Tables shouldn’t imply relationships that don’t exist.
The example here was the job_skills table, where a single row listed one of a person’s technology skills (like ‘Linux’) and one of their language skills (like ‘French’). This made it look like their Linux knowledge was specific to French, or vice versa … when that isn’t the case in the real world. Normalizing this involved splitting the tech skills and job skills into separate tables.
Creating database and table:
creating table
Different DB vendors (mysql/psql/sqllite)have different type support (some have ip address as type some uses string). you need to read the manual for exact type
You can create new table from your APP code, but don’t do that. create table upfront using sql commands.
Do you need to create multipe DB in your app.what are the benefits?
Multiple DB are easy to maintain/roolback independly. So partition your table in multiple DB’s if they represent independent functionality of your product. Think if you can create multiple DB instances 1 for each customer, provided amount of data for each of your customer is big enough. This is good for backup(restore), Security and scalability
you can do select and join across the DB’s iff both the DB’s are stroed in same machine.
usually migrating a DB from one disk to other (machine) is as easy as running few commands like
mysqldump -u root -p --opt [database name] > [database name].sqlscp [database name].sql [username]@[servername]:path/to/database/scp newdatabase.sql user@example.com:~/mysql -u root -p newdatabase < /path/to/newdatabase.sql
Declaring Primary Key
Inserting a duplicate key in primary key will raise an exception (error) in DB
Adding constraints (referential integrity constraint). THIS create a foreign key.
DB will flag error if it find a dangling reference!
Many to many relation modelled with the help of table and foriegn key
Notice that STUDENT and COURSES have many to many relationship so we have to create a third table.(grades) below!
You can probably create foreign key in student for courses and foreign key in courses for student but then it will make these table non-normalized. Think what will be the primary key in this case?
Also noitce that in the above “Grades” table Student_id+course should be a primary key as a whole. Their combination as a whole is unique not individually!
Schema for a blog design
Notice that you have a composite primary key above in the votes table!
Self Joins
How do you find out student which are living in same room?
select a.id, b.id, a.building, a.room
from residences as a, residences as b
where a.building = b.building
and a.room = b.room and a.id < b.id
order by a.building, a.room;
What is a best way to represent a tree kind of structure in sql.This is way to common in practise (imagine blog nested comments?
Nested set model. This is bit complicated but the following blog talks about it in details
Counting query
select count(*) from animals;
-- returns the number of animals in the zoo
select count(*) from animals where species = 'gorilla';
-- returns the number of gorillas
select species, count(*) from animals group by species;
Creating Views in sql
