So we've looked at databases and we've looked at how to create tables, how to insert data, update
data, delete data, select data, that sort of thing, but so far we've just worked with test tables
and none of them are really useful for our application.
So what I want to do now is actually to outline the structure of our database.
And normally when I do this, I use pen and paper.
I'm kind of old school that way, but I can't figure out an easy way to record myself with a pen and
paper that will actually work for these videos.
So instead, I'm using a tool, a tool called Siecle Ed, and it's a commercial product.
I only bought it for this course.
I normally don't do it.
Don't do my schemas using a piece of software.
I just use pen and paper.
But this will be enough to actually show you how I go about determining what the structure of my database
is going to be.
So don't pay any attention to the actual tool itself or to any errors it might display, because I'm
just using this to show how you might work on the schema for your database.
So let's think about what we actually need.
First of all, I'm going to add a table and I'm going to add a table that I'm going to call users.
So I'll give the table a name users and notice I do use plural when I'm talking for most.
Most of my tables will have the plural word for their name and you'll see why when we get to a developing
database.
Models now, this table is going to have to have several rows, of course.
So let's add a field to it.
And the first one will be an ID and I'll call that an intelligent.
It'll actually be a serial in our final database structure and auto incrementing field and it will be
an unsigned integer of length 11.
So that will be the ID that I can find a given user for.
And when I talk about users here, I'm not talking about people who are making reservations.
I'm talking about the people who will log into the back end.
We're going to build later in this course to review reservations, to modify them, delete them, whatever
the case may be.
In other words, the property owner in the case of our application and I need some information about
those users.
So I'm going to need another field here and I'll call that field first name and I'll make it a vaka
255.
That's long enough to hold a first name.
Let's give them a last name.
And it also will be of very character, varying of our car, 255.
And then I need something to allow those users to log in.
So what is a piece of information that's going to be unique to a given user?
No two users will have this same piece of information.
Well, the easiest thing, of course, is an email address.
So let's add an email address, which I'll just call email and I'll make it vaka and I'll make it 255
again.
And then they have to have a password.
Now, we are not going to store passwords in the database at all.
That is a terrible idea.
And if you've been paying attention to the news at all for the last decade or so, invariably every
year someone's database is compromised by an attacker and passwords are released out into the wild.
And that's bad.
We're going to store instead a hash of the password, but I'm going to create another field and I will
call that field password, even though it's going to hold a password hash.
And I'll make that right now.
I'll make it a Barpak 255.
That'll probably change, but I'm just outlining the structure of our database.
OK, so a 255 character password.
Is there anything else I should store here?
Well, it's always a good idea to keep track of when a particular piece of information in a database
was created and when it was last updated.
So I will create two new fields created at which I'll make a timestamp and that will store the date
and time that this particular row was created.
And I'll add one more file called Updated App, and I'll make that again, a timestamp.
And the last thing I'm going to stick in here is an access level, because there may be situations where
I want different users of the back end to have different permissions and an easy way to do that.
It's just to determine what their access level might be.
And I'll just make that for now.
So I'll call it access level and it'll be an integer size 11.
All right.
So, for example, someone with an access level of three might be a super user and they can do everything.
Someone with an access level of two can do some things, but not everything that a super user can do,
so on and so forth.
So I'm not sure what those actually level access levels are going to be right now, but I know they're
going to exist.
So that's probably enough for our user table right now.
So I'll stick that over here just to get it out of the way.
Now, what other kinds of information are we going to need to keep track of?
Well, obviously reservations, so let's create a new table and I'll drag it over here just so we can
put everything on the same screen at the same time.
And I will call this reservations, plural, risc our Vatican reservations that will have a number of
fields.
Obviously it's going to have an ID field, which I'll make it into LeBon unsigned so you can't have
negative numbers in there.
And what kind of information are we storing for a given reservation?
Well, there's the information we have on our form that we created a while ago, but there's actually
more information we're going to need.
Let's do some obvious things.
The guest will have a first name and I'll make that of our car to 55.
The guest will have a last name.
And again, character varying.
255 is sufficient for that, they're going to have an email address, which I'll call email and make
that a character very 255.
They're going to have a phone number, which I'll just call phone.
And again, I'll make it a character bearing 235.
We also need to know what room they're going to stay in and how are we going to do that?
Well, I'm not sure.
Let's get back to that in a minute, OK?
Well, they're definitely going to have a start date and an end date for their stay.
So we'll say start date, which will just be a date and they'll have an end date.
Which will also be a date.
And we also need the room they're going to stay in and I'm not sure how I'm going to do that.
Well, let's just create another table right now, and I'll put it right beside this one and I'll call
it rooms.
And this table will hold information about all the various rooms that we have.
So let's add field and the first one, of course, will be it won't make it into 11 unsigned and a room
name, which I'll just call room names and I'll make that a character very 255.
And that might be enough.
I probably could store price and things like that in there, but I'm just going to leave those like
they are for right now.
And we can modify this table later if we need to.
And now I need a link between this table reservations and this table rooms.
So how can I determine which reservation or which room a given reservation is tied to?
Well, I can do that by adding a field here and I'll call it room ID and it will be of type into 11
unsigned.
And then I'll put a link between this room idy and whatever ID the room they're staying in happens to
be.
And in this particular tool I can do it by dragging a line from here to here.
And now we have what's called a foreign key relationship between the field ID and the field ID in the
table rooms and foreign keys actually are pretty cool.
They will prevent you from accidentally entering a value in this table that doesn't exist in this table.
We can put constraints on foreign key relationships and we'll do that when we actually build these tables,
which will do very soon.
But for right now, just bear in mind that the link between where a given reservation is, what room
it's for, and the rooms table is done by having a link between this column remedy in the table reservations
and this column rooms in the rooms table the ID column in the room table.
So there's a foreign key relationship and that will be very useful for us that helps us enforce something
called referential integrity in our database structure.
So you can't have if you only have ID one in 82 for rooms, for example, you can't accidentally put
ID zero or ID three in the reservations table in the column room.
So that's a very useful way of enforcing integrity in our database.
So so far, I have a user's table, I have a room table and I have a reservation table.
And one thing that seems to be missing at this point is a means of determining whether or not a given
room is available on a given day.
And we're going to do that by creating another table, which I'll call room restrictions,
and I'll put that right here so we can actually see it a little bit.
Now, this obviously is going to have its own columns.
First one is going to have is the same one we've been using right along called ID and I'll make that
into level unsigned.
And then we're going to store in this table several pieces of information.
First of all, let's put in two dates, start date, which will be of type date
and end date.
Which will also be of tight date, and then we'll add another one room remedy.
Which will be of type int 11, unsigned and finally reservation ID, which will also be of type int
eleven unsigned, and then I'll put in those two fields that I said we really should keep track of and
field created at, which is of type timestamp
updated at,
which is a type timestamp.
And that's probably enough for right now.
As you've probably guessed, the room ID is also a foreign key to the column ID in the table rooms,
and in this case, reservation ID is a foreign key to the column ID in reservations.
So now I probably should put, created and updated as in here to let's do it before I forget.
So Attfield created at which will be a timestamp and Arrowfield updated at which would be a timestamp
and the same thing over here and field created at time and updated us which will be a timestamp.
All right.
So now I have four tables.
Does this cover all of the information we're probably going to need to store in our database?
Well, no, not quite.
So let's think this through.
Is there any situation where a room might not be available even though there's not a reservation?
And in fact, there is suppose there's being some maintenance is being done on that room?
Or suppose the property owner wants to go away for two weeks in December and not rent anything during
the two weeks that they're away because there'll be no staff on hand.
So what we can do is add another piece of information to our room restrictions table that determines
what kind of restriction it is.
So I will Arrowfield and I will call it restriction ID and I'll make it into 11 on site and I'll create
one more table called restriction types or just call it restrictions.
That's even better restrictions and it will have an ID field, which will be an unsigned integer size
11, and we'll call the other field restriction name.
It'll be a marker 255 and then our standard created an updated height and field created at time stamp
and add field updated at Timestamp, and then we'll make a foreign key relationship between restriction
ID and the ID column of the restrictions table.
All right.
Now, let me drag these things around a little bit so it's easier to see how everything is related to
everything else.
So here is potentially a workable database schema.
Will this store all of the information we need in order to complete building our application?
Well, it probably will.
And there may be other information I want to store.
Maybe I want to display the price of a room or of a given stay in a given room.
So then I'd have to have a price for a room so I could put a price column right in the rooms table.
Or I could create another table called prices and allow different prices for different times of the
year.
But for our purposes, in learning how to build a Web application, this is probably sufficient for
right now.
So we wind up having a users table that will allow our property owner to authenticate and log into a
back end.
We have a means of storing reservations and storing rooms and determining whether or not a room is available.
Now, let's talk about this one a little bit more.
The room restrictions, what purpose does this table serve?
Well, imagine, if you will, that both rooms are completely available right now for every day in the
future.
And then someone comes to our Web site and they make a reservation, say, for June the 1st to June
the 3rd, 2012 in the generals quarters, not 2012.
2012 is in the past.
Time travel hasn't been invented yet.
Let's try that again, June 1st to June the 3rd, 2022, which is in the future right now as I record
this.
So when they do that, we save the reservation information and we insert a row in the room restrictions
table that says hey for room Itaewon one, which might be the generals quarters from June the 1st to
June the 3rd of 2022, we're going to put in a restriction for this room tied to this reservation instead
of type whatever type we've determined the restriction is for a booking.
We just enter a row in there.
So now we have one row and our room restrictions table that says the room is booked from this date to
this date.
The next time we check to see if a room is available, we fire a query off to this table saying, hey,
someone wants to stay from this date, to this date, in this room, do you have any rows in the room
restriction room restrictions table where the dates that I want are overlapped by any of the restrictions,
any of the rows in your database for this given room?
So if I get a non-zero response, I get yes, I have two rows that are overlapping the dates that you
want, then I know the room is not available for those dates.
But if I get zero, no, there are no rows that that overlap the date you're requesting for this particular
room, then I know the room is available.
That's the only purpose that the room restrictions table service.
It lets us query the database to find out whether or not a given room is available.
So when we actually write the query to do that search, this will become very, very clear.
But hopefully you understand the purpose that this table serves.
So right now I have a workable database schema.
So what we need to do is to go and create these tables.
Now, what I'm not going to force us to do is to go and build these tables by hand using Dvor or any
database tool.
Instead, we're going to use database migration's we're going to take advantage of a tool where we can
describe our our tables using a simple go syntax and then run a tool that will create those tables for
us.
And the advantage of this is that we have stored in our git repository and as part of our application,
a means of recreating our database structure any time we want to.
And as our database structure changes over time and it will, we can simply write new migrations that
allow us to apply our changes to our database and ensure that we never have to go and create a database
manually.
We can instead describe it programmatically, encode and run migrations up to apply them or down to
reverse them.
And this is a really good way of ensuring that your database will always be under control and will never
just kind of grow organically and and fall out of sync with your code base.
So we'll take care of that in the next few lecture's.