Newby to SQL, creating simple Hotel Database/Housekeeping Database

[kldr1101]

Hey guys, just wondering if you could all point me in the right direction here. I'm just starting out using Microsoft SQL server Management studio, just trying to make a basic database for Hotel/Housekeeping, any tips, suggestions would help!! Thanks in advance!

Here is my code so for:

CREATE DATABASE HOTELHOUSEKEEPING

CREATE TABLE RESERVATION
(
RESERVATION_ID int NOT NULL,
ROOM_NUM int NOT NULL,
RESERVATION_NAME varchar(100) NOT NULL,
HOUSEKEEPING_STATUS int NOT NULL,
RESERVATION_START datetime NOT NULL,
RESERVATION_END datetime NOT NULL,
CONSTRAINT "PK_RESERVATION" PRIMARY KEY CLUSTERED
("RESERVATION_ID"),
);

CREATE TABLE ROOMS
(
ROOM_NUM int NOT NULL,
ROOM_SIZE varchar(5) NOT NULL,
HOUSEKEEPING_STATUS int NOT NULL,
CONSTRAINT "PK_ROOMS" PRIMARY KEY CLUSTERED
("ROOM_NUM"),

);

CREATE TABLE HOUSEKEEPING
(
HOUSEKEEPING_STATUS int not null,
ROOM_NUM int not null,
RESERVATION_ID int NULL,
CONSTRAINT "PK_HOUSEKEEPING_STATUS" PRIMARY KEY CLUSTERED
("HOUSEKEEPING_STATUS"),
);

ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_ROOMS" FOREIGN KEY
("ROOM_NUM") REFERENCES "dbo"."ROOMS" ("ROOM_NUM");

ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_HOUSEKEEPING" FOREIGN KEY
("HOUSEKEEPING_STATUS") REFERENCES "dbo"."HOUSEKEEPING" ("HOUSEKEEPING_STATUS");

ALTER TABLE HOUSEKEEPING
ADD CONSTRAINT "FK_HOUSEKEEPING_ROOMS" FOREIGN KEY
("ROOM_NUM") REFERENCES "dbo"."ROOMS" ("ROOM_NUM");

ALTER TABLE ROOMS
ADD CONSTRAINT "FK_ROOMS_HOUSEKEEPING" FOREIGN KEY
("HOUSEKEEPING_STATUS") REFERENCES "dbo"."HOUSEKEEPING" ("HOUSEKEEPING_STATUS");

INSERT "RESERVATION" VALUES('11',’101’,’Drew Klemz’,Clean’,’12/01/2016’,’12/12/2016’)
INSERT "RESERVATION" VALUES('21',’102’,’Bill Johnson’,Clean’,’11/30/2016’,’12/12/2016’)
INSERT "RESERVATION" VALUES('31',’103’,’Jason Frees’,Clean’,’12/01/2016’,’12/10/2016’)
INSERT "RESERVATION" VALUES('41',’104’,’Sarah Gieseke’,Clean’,’12/01/2016’,’12/07/2016’)
INSERT "RESERVATION" VALUES('51',’105’,’Tony Newell’,Clean’,’12/01/2016’,’12/22/2016’)

INSERT "ROOMS" VALUES(’101’,’Queen’,’Clean’)
INSERT "ROOMS" VALUES(’102’,’King’,’Clean’)
INSERT "ROOMS" VALUES(’103’,’King’,’Clean’)
INSERT "ROOMS" VALUES(’104’,’Queen’,’Clean’)
INSERT "ROOMS" VALUES(’105’,’King’,’Clean’)
INSERT "ROOMS" VALUES(’106’,’King’,’Clean’)
INSERT "ROOMS" VALUES(’201’,’Queen’,’Clean’)
INSERT "ROOMS" VALUES(’202’,’Queen’,’Dirty’)
INSERT "ROOMS" VALUES(’203’,’Queen’,’Dirty’)
INSERT "ROOMS" VALUES(’204’,’King’,’Dirty’)

INSERT "HOUSEKEEPING" VALUES(’Dirty’,’101’,’11’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’102’,’21’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’103’,’31’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’104’,’41’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’105’,’51’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’106’,’’)
INSERT "HOUSEKEEPING" VALUES(’Clean’,’201’,’’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’202’,’’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’203’,’’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’204’,’’)

Also I have a few business rules I have to follow that I am trying to create
• A Room cannot be Occupied and clean, but a room can be Not occupied and Not clean, meaning it needs to be cleaned.
• Room that is occupied and dirty can request housekeeping, meaning change sheets, pillow cases, fresh towels, etc (this is what Housekeeping does)
• A room can only have one Customer, but a customer may have many rooms.

I also have to make some views, I think I know how to make these but I want to make sure I'm on the right track before continuing.

[gbritton]

basically not too bad. You don't need to double-quote tables and column names though. It would look cleaner if you didn't

Some things I wonder about:

1. Should the Rooms table hold the clean/dirty flag? I'd probably just keep that to the housekeeping table and join with the Rooms table to get a list of clean rooms with details.
2. Should the reservation table have the Reservation Name column or should there be another table, say "Guests" that has that info? The reservation table could have a FK to the Guests table, which could then hold other info (e.g. Phone Number, Email address)

[kldr1101]

Hey gbritton thanks for the help.

1: So what you are saying is take the HOUSEKEEPING_STATUS out of rooms, and just leave it in the Housekeeping table. So if I just join room_num to Housekeeping as a foreign key these will link together to show when one is dirty or not?

2. So what I should do here is create a Guests table, link the Reservation_Name as a Foreign to key from Reservation Over to a guests table, which could hold more information. This is a good Idea I will do this too!

I'll make a new code and post it.

Also when you are saying I don't have to double quote you mean I could just do
INSERT HOUSEKEEPING VALUES('Dirty','101','11') ?

[gbritton]

@kldr1101

Also when you are saying I don't have to double quote you mean I could just do INSERT HOUSEKEEPING VALUES('Dirty','101','11') ?

yup!

[ScottPletcher]

First thing, and by far the most importantly:

Do the logical model first!

Have you done a logical model (entities/attributes) already or are you starting at the physical model (tables/columns)?

This is far more than just semantics, it's often critical to properly recognizing data relationships and getting the ultimate physical tables properly normalized.

Logical modeling is a huge topic. But, in very short form, you use business, not technical, entities and assign the necessary data to each one. You determine relationships between entities, such as:
"A Room has 0 or 1 active Reservations"
"A Room has 0 or more inactive Reservations"
etc.

At this stage, keep it in business terms. Technical considerations -- indexes, constraints, etc. -- come later.

[kldr1101]

Hey guys thanks for your help so far.

ScottPletcher, I did create a business rule, but as I started coding I realized right away that I had to change things that I thought would work that did not make sense for a technical standpoint, just trying to get my feet wet a little bit with SQL, maybe for my next project I'll do this in the correct order.

Here is my 'new ' code how does this look?

CREATE DATABASE HOTELHOUSEKEEPING

GO

USE HOTELHOUSEKEEPING

CREATE TABLE GUESTS
(
GUEST_LNAME varchar(15) NOT NULL,
GUEST_FNAME varchar(15) NOT NULL,
GUEST_PHONE int(10) NOT NULL,
GUEST_LICENSEPLATE varchar(6) NOT NULL,
CONSTRAINT "PK_GUESTS" PRIMARY KEY CLUSTERED
("GUEST_LNAME"),
);

CREATE TABLE RESERVATION
(
RESERVATION_ID int NOT NULL,
ROOM_NUM int NOT NULL,
GUEST_LNAME varchar(15) NOT NULL,
HOUSEKEEPING_STATUS int NOT NULL,
RESERVATION_START datetime NOT NULL,
RESERVATION_END datetime NOT NULL,
CONSTRAINT "PK_RESERVATION" PRIMARY KEY CLUSTERED
("RESERVATION_ID"),
);

CREATE TABLE ROOMS
(
ROOM_NUM int NOT NULL,
ROOM_SIZE varchar(5) NOT NULL,
CONSTRAINT "PK_ROOMS" PRIMARY KEY CLUSTERED
("ROOM_NUM"),
);

CREATE TABLE HOUSEKEEPING
(
HOUSEKEEPING_STATUS int not null,
ROOM_NUM int not null,
RESERVATION_ID int NULL,
CONSTRAINT "PK_HOUSEKEEPING_STATUS" PRIMARY KEY CLUSTERED
("HOUSEKEEPING_STATUS"),
);

ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_ROOMS" FOREIGN KEY
("ROOM_NUM") REFERENCES "dbo"."ROOMS" ("ROOM_NUM");

ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_HOUSEKEEPING" FOREIGN KEY
("HOUSEKEEPING_STATUS") REFERENCES "dbo"."HOUSEKEEPING" ("HOUSEKEEPING_STATUS");

ALTER TABLE RESERVATION
ADD CONSTRAINT “FK_RESERVATION_GUESTS" FOREIGN KEY
("GUEST_LNAME") REFERENCES "dbo"."GUESTS" ("GUEST_LNAME");

ALTER TABLE HOUSEKEEPING
ADD CONSTRAINT "FK_HOUSEKEEPING_ROOMS" FOREIGN KEY
("ROOM_NUM") REFERENCES "dbo"."ROOMS" ("ROOM_NUM");

INSERT INTO GUESTS VALUES('Klemz','Drew','7635492145','946GBA')
INSERT INTO GUESTS VALUES('Johnson','Bill','4125486321','435NAT')
INSERT INTO GUESTS VALUES('Frees','Jason','3204896123','716KAT')
INSERT INTO GUESTS VALUES('Samps','Sarah','7635674895','943TOA')
INSERT INTO GUESTS VALUES('Newell','Tony','2197514896','641TOD')

INSERT INTO RESERVATION VALUES('11',’101’,’Klemz’,Clean’,’12/01/2016’,’12/17/2016’)
INSERT INTO RESERVATION VALUES ('21',’102’,’Johnson’,Clean’,’11/30/2016’,’12/20/2016’)
INSERT INTO RESERVATION VALUES ('31',’103’,’Frees’,Clean’,’12/01/2016’,’12/10/2016’)
INSERT INTO RESERVATION VALUES ('41',’104’,’Samps’,Clean’,’12/01/2016’,’12/10/2016’)
INSERT INTO RESERVATION VALUES ('51',’105’,’Newell’,Clean’,’12/01/2016’,’12/22/2016’)

INSERT INTO ROOMS VALUES(’101’,’Queen’,’Clean’)
INSERT INTO ROOMS VALUES (’102’,’King’,’Clean’)
INSERT INTO ROOMS VALUES (’103’,’King’,’Clean’)
INSERT INTO ROOMS VALUES (’104’,’Queen’,’Clean’)
INSERT INTO ROOMS VALUES (’105’,’King’,’Clean’)
INSERT INTO ROOMS VALUES (’106’,’King’,’Clean’)
INSERT INTO ROOMS VALUES (’201’,’Queen’,’Clean’)
INSERT INTO ROOMS VALUES (’202’,’Queen’,’Dirty’)
INSERT INTO ROOMS VALUES (’203’,’Queen’,’Dirty’)
INSERT INTO ROOMS VALUES (’204’,’King’,’Dirty’)

INSERT INTO HOUSEKEEPING VALUES(’Dirty’,’101’,’11’)
INSERT INTO HOUSEKEEPING VALUES (’Dirty’,’102’,’21’)
INSERT INTO HOUSEKEEPING VALUES (’Dirty’,’103’,’31’)
INSERT INTO HOUSEKEEPING VALUES (’Dirty’,’104’,’41’)
INSERT INTO HOUSEKEEPING VALUES (’Dirty’,’105’,’51’)
INSERT INTO HOUSEKEEPING VALUES (’Dirty’,’106’,’’)
INSERT INTO HOUSEKEEPING VALUES (’Clean’,’201’,’’)
INSERT INTO HOUSEKEEPING VALUES (’Dirty’,’202’,’’)
INSERT INTO HOUSEKEEPING VALUES (’Dirty’,’203’,’’)
INSERT INTO HOUSEKEEPING VALUES (’Dirty’,’204’,’’)

I have a few questions, what do I write to make HOUSEKEEPING_STATUS as 0=Clean and 1=Dirty?
Or is there an easier way? or should I just leave it?

Also how do I make it so when someone gets assigned a room that it automatically changes that room to "Dirty" rather than clean?

And for a room can only have one customer but a customer can have many rooms, I shouldn't have to change anything on my code for this correct?

[gbritton]

@kldr1101

I have a few questions, what do I write to make HOUSEKEEPING_STATUS as 0=Clean and 1=Dirty?Or is there an easier way? or should I just leave it?

If there are (and always will be) only two states, use a bit (0 = clean, 1 = dirty). Otherwise consider a one-byte character (e.g. 'C' = clean, "D" = dirt, "S" = So-so etc)

@kldr1101

Also how do I make it so when someone gets assigned a room that it automatically changes that room to "Dirty" rather than clean

You could either do it with a trigger, or encapsulate the business logic in a stored procedure with appropriate transactions. I'd probably do the latter.

@kldr1101

And for a room can only have one customer but a customer can have many rooms, I shouldn't have to change anything on my code for this correct?

you could make a UNIQUE constraint on the reservation table consisting of the room # and guest id. BTW I would not put the guest name in the reservation table. Add an id column to the guest table and an FK in the reservation table to point to it.

I'm guessing you're not writing your own full-fledged reservation system here. Real world examples tend to be a good bit more complicated!

[kldr1101]

I changed the guest table to have a GUEST_ID as the primary key, and a FK to Reservation.

Here is my new code:

CREATE DATABASE HOTELHOUSEKEEPING

GO

USE HOTELHOUSEKEEPING

CREATE TABLE GUESTS
(
GUEST_ID int NOT NULL,
GUEST_LNAME varchar(15) NOT NULL,
GUEST_FNAME varchar(15) NOT NULL,
GUEST_PHONE varchar(10) NOT NULL,
GUEST_LICENSEPLATE varchar(6) NOT NULL,
CONSTRAINT "PK_GUESTS" PRIMARY KEY CLUSTERED
("GUEST_ID"),
);

CREATE TABLE RESERVATION
(
RESERVATION_ID int NOT NULL,
ROOM_NUM int NOT NULL,
GUEST_ID int NOT NULL,
HOUSEKEEPING_STATUS bit NOT NULL,
RESERVATION_START datetime NOT NULL,
RESERVATION_END datetime NOT NULL,
CONSTRAINT "PK_RESERVATION" PRIMARY KEY CLUSTERED
("RESERVATION_ID"),
);

CREATE TABLE ROOMS
(
ROOM_NUM int NOT NULL,
ROOM_SIZE varchar(5) NOT NULL,
CONSTRAINT "PK_ROOMS" PRIMARY KEY CLUSTERED
("ROOM_NUM"),
);

CREATE TABLE HOUSEKEEPING
(
HOUSEKEEPING_STATUS bit not null,
ROOM_NUM int not null,
RESERVATION_ID int NULL,
CONSTRAINT "PK_HOUSEKEEPING_STATUS" PRIMARY KEY CLUSTERED
("HOUSEKEEPING_STATUS"),
);

ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_ROOMS" FOREIGN KEY
("ROOM_NUM") REFERENCES "dbo"."ROOMS" ("ROOM_NUM");

ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_HOUSEKEEPING" FOREIGN KEY
("HOUSEKEEPING_STATUS") REFERENCES "dbo"."HOUSEKEEPING" ("HOUSEKEEPING_STATUS");

ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_GUESTS" FOREIGN KEY
("GUEST_ID") REFERENCES "dbo"."GUESTS" ("GUEST_ID");

ALTER TABLE HOUSEKEEPING
ADD CONSTRAINT "FK_HOUSEKEEPING_ROOMS" FOREIGN KEY
("ROOM_NUM") REFERENCES "dbo"."ROOMS" ("ROOM_NUM");

INSERT INTO GUESTS VALUES(50,'Klemz','Drew','7635492145','946GBA')
INSERT INTO GUESTS VALUES(72,'Johnson','Bill','4125486321','435NAT')
INSERT INTO GUESTS VALUES(921,'Frees','Jason','3204896123','716KAT')
INSERT INTO GUESTS VALUES(57,'Samps','Sarah','7635674895','943TOA')
INSERT INTO GUESTS VALUES(681,'Newell','Tony','2197514896','641TOD')

INSERT INTO RESERVATION VALUES(11,101,50,'0','12/01/2016','12/17/2016')
INSERT INTO RESERVATION VALUES(21,102,72,'0','11/30/2016','12/20/2016')
INSERT INTO RESERVATION VALUES(31,103,921,'0','12/01/2016','12/10/2016')
INSERT INTO RESERVATION VALUES(41,104,57,'0','12/01/2016','12/10/2016')
INSERT INTO RESERVATION VALUES(51,105,681,'0','12/10/2016','12/22/2016')

INSERT INTO ROOMS VALUES(101,'Queen')
INSERT INTO ROOMS VALUES(102,'King')
INSERT INTO ROOMS VALUES(103,'King')
INSERT INTO ROOMS VALUES(104,'Queen')
INSERT INTO ROOMS VALUES(105,'King')
INSERT INTO ROOMS VALUES(106,'King')
INSERT INTO ROOMS VALUES(201,'Queen')
INSERT INTO ROOMS VALUES(202,'Queen')
INSERT INTO ROOMS VALUES(203,'Queen')
INSERT INTO ROOMS VALUES(204,'King')

INSERT INTO HOUSEKEEPING VALUES('0',101,11)
INSERT INTO HOUSEKEEPING VALUES('0',102,21)
INSERT INTO HOUSEKEEPING VALUES('0',103,31)
INSERT INTO HOUSEKEEPING VALUES('0',104,41)
INSERT INTO HOUSEKEEPING VALUES('0',105,51)
INSERT INTO HOUSEKEEPING VALUES('1',106,'')
INSERT INTO HOUSEKEEPING VALUES('0',201,'')
INSERT INTO HOUSEKEEPING VALUES('0',202,'')
INSERT INTO HOUSEKEEPING VALUES('1',203,'')
INSERT INTO HOUSEKEEPING VALUES('1',204,'')

and now I'm getting errors:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 73
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_RESERVATION_ROOMS". The conflict occurred in database "HOTELHOUSEKEEPING", table "dbo.ROOMS", column 'ROOM_NUM'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 74
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_RESERVATION_ROOMS". The conflict occurred in database "HOTELHOUSEKEEPING", table "dbo.ROOMS", column 'ROOM_NUM'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 75
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_RESERVATION_ROOMS". The conflict occurred in database "HOTELHOUSEKEEPING", table "dbo.ROOMS", column 'ROOM_NUM'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 76
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_RESERVATION_ROOMS". The conflict occurred in database "HOTELHOUSEKEEPING", table "dbo.ROOMS", column 'ROOM_NUM'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 77
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_RESERVATION_ROOMS". The conflict occurred in database "HOTELHOUSEKEEPING", table "dbo.ROOMS", column 'ROOM_NUM'.
The statement has been terminated.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 91
Violation of PRIMARY KEY constraint 'PK_HOUSEKEEPING_STATUS'. Cannot insert duplicate key in object 'dbo.HOUSEKEEPING'. The duplicate key value is (0).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 92
Violation of PRIMARY KEY constraint 'PK_HOUSEKEEPING_STATUS'. Cannot insert duplicate key in object 'dbo.HOUSEKEEPING'. The duplicate key value is (0).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 93
Violation of PRIMARY KEY constraint 'PK_HOUSEKEEPING_STATUS'. Cannot insert duplicate key in object 'dbo.HOUSEKEEPING'. The duplicate key value is (0).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 94
Violation of PRIMARY KEY constraint 'PK_HOUSEKEEPING_STATUS'. Cannot insert duplicate key in object 'dbo.HOUSEKEEPING'. The duplicate key value is (0).
The statement has been terminated.

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 96
Violation of PRIMARY KEY constraint 'PK_HOUSEKEEPING_STATUS'. Cannot insert duplicate key in object 'dbo.HOUSEKEEPING'. The duplicate key value is (0).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 97
Violation of PRIMARY KEY constraint 'PK_HOUSEKEEPING_STATUS'. Cannot insert duplicate key in object 'dbo.HOUSEKEEPING'. The duplicate key value is (0).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 98
Violation of PRIMARY KEY constraint 'PK_HOUSEKEEPING_STATUS'. Cannot insert duplicate key in object 'dbo.HOUSEKEEPING'. The duplicate key value is (1).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 99
Violation of PRIMARY KEY constraint 'PK_HOUSEKEEPING_STATUS'. Cannot insert duplicate key in object 'dbo.HOUSEKEEPING'. The duplicate key value is (1).
The statement has been terminated.

[Kristen]

RESERVATION has FKeys to Rooms, Housekeeping and Guests.

The INSERTS into RESERVATION happen after ROOMS ... but you will need to move inserts into HOUSEKEEPING and GUESTS to be done before RESERVATION

@kldr1101

Violation of PRIMARY KEY constraint 'PK_HOUSEKEEPING_STATUS'. Cannot insert duplicate key in object 'dbo.HOUSEKEEPING'. The duplicate key value is (0).

HOUSEKEEPIN has a PKey of HOUSEKEEPING_STATUS - that's the first column in your INSERT so that needs to be unique.

[kldr1101]

@Kristen

The INSERTS into RESERVATION happen after ROOMS ... but you will need to move inserts into HOUSEKEEPING and GUESTS to be done before RESERVATION

So are you saying I am getting this error because of the order I have my INSERT INTO commands in?

[gbritton]

yup, that's it. (also why I usually put biz logic like this in a stored proc)

[ScottPletcher]

I still feel the data model / structure needs some work. At any rate, here's some very quick notes from me as I look it. I've used numbers and letters to facilitate possible discussion of the points without having to repeat the text itself.

1. GUESTS table:
   1A) Other than ID, the "GUEST_" prefix should be dropped from the column names: for example, simply LAST_NAME (not LNAME), FIRST_NAME, etc.
   1B) LICENSE_PLATE_STATE must be included (at least); LICENSE_PLATE by itself could be useless. Length should be increased to at least 7 chars, since many states have 7 char license plates (Calif for one, I think); I'd probably use 10 or even 16 just in case. [Actually, I would put LICENSE_PLATE in a separate table, since in theory guests in the same room could have different vehicles, but that's another level of discussion.]

2. RESERVATION table:
   2A) The HOUSEKEEPING_STATUS column should not be in this table, since it does not depend on a reservation, it depends on the room itself.

3. ROOMS table:
   3A) ROOM_SIZE should be a numeric code and not a text string. A tinyint value, 0-255, would almost certainly be plenty of possible values. A separate lookup table would store the meaning of the code. One added flexibility with that approach is that the code can be further described in the lookup table itself. For example, for some hotels, "small" might be 75 sq. ft.(sorry, I'm American, so not sq. m. ), whereas for a ritzy location "small" might be 300 sq. ft..
   3B) HOUSEKEEPING_STATUS I agree should be a code, but int is far too large a value. There can't possibly be 2 billion(!) housekeeping statuses. Personally, I'd be inclined to put all room statuses in a separate table, to allow for multiple possibilities, but I don't think your current model requires this (although, as you get further into the exercise you are doing, it just might!).

4. HOUSEKEEPING: I admit I don't see the need for this table at all as it is named / structured.

[Kristen]

@kldr1101

I am getting this error because of the order I have my INSERT INTO commands in?

Yes. Your RESERVATION reference ROOMS.ROOM_NUM, HOUSEKEEPING.HOUSEKEEPING_STATUS and GUESTS.GUEST_ID, the Foreign Key constraints require that those corresponding rows must exist. You cannot create a RESERVATION record before the corresponding records in the other table exist. It is very important that referential integrity is maintained in a database - it is no use having a RESERVATION that references ROOM_NUM=1234 if a record for that room does not exist - your would wind up with any old rubbish, and thereby Orphan Data, in the database.

Instead of changing the order of the INSERTS another solution is to

1. Insert the data
   and then
2. Create the Foreign Keys afterwards.

You could change your script to do that if you prefer. If you accidentally create a Reservation for a Room that does not exist when you then create the Foreign Key you will get an appropriate error message.

This is usually only a problem during initial bulk-loading of the database - particular where there is a circular reference between some tables. Once the database is populated adding a new RESERVATION would probably use picklists / predictive-text / validation of some sort, and combined with the work-flow (Create Guest before Reservation) and therefore the ROOM_NUM and GUEST_ID would be pre-existing records when the RESERVATION record was created. The Foreign Key constraints would still be there just in case something went wrong - program bug, attempt to delete data that was dependent on other data, hacking, etc.

[kldr1101]

I have now deleted the reservations table as I agree, it didn't serve much of a purpose, also added license plate state, as to give license plate more of a meaning.

Here is what I have now:
I am going to be adding some views, and some other things to it tomorrow, and may have questions, but any comments or suggestions would be helpful thanks!

CREATE DATABASE HOTELHOUSEKEEPING

GO

USE HOTELHOUSEKEEPING

CREATE TABLE GUESTS
(
GUEST_ID int NOT NULL,
GUEST_LNAME varchar(15) NOT NULL,
GUEST_FNAME varchar(15) NOT NULL,
GUEST_PHONE varchar(10) NOT NULL,
GUEST_LICENSEPLATE varchar(10) NULL,
GUEST_LICENSEPLATE_STATE varchar(6) NULL,

CONSTRAINT "PK_GUESTS" PRIMARY KEY CLUSTERED
("GUEST_ID"),
);

CREATE TABLE RESERVATION
(
RESERVATION_ID int NOT NULL,
ROOM_NUM int NOT NULL,
GUEST_ID int NOT NULL,
RESERVATION_START datetime NOT NULL,
RESERVATION_END datetime NOT NULL,
CONSTRAINT "PK_RESERVATION" PRIMARY KEY CLUSTERED
("RESERVATION_ID"),
);

CREATE TABLE ROOMS
(
ROOM_NUM int NOT NULL,
ROOM_SIZE varchar(5) NOT NULL,
ROOM_STATUS bit NOT NULL,
CONSTRAINT "PK_ROOMS" PRIMARY KEY CLUSTERED
("ROOM_NUM"),
);

ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_ROOMS" FOREIGN KEY
("ROOM_NUM") REFERENCES "dbo"."ROOMS" ("ROOM_NUM");

ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_GUESTS" FOREIGN KEY
("GUEST_ID") REFERENCES "dbo"."GUESTS" ("GUEST_ID");

INSERT INTO GUESTS VALUES(50,'Klemz','Drew','7635492145','946GBA','MN')
INSERT INTO GUESTS VALUES(72,'Johnson','Bill','4125486321','435NAT','WI')
INSERT INTO GUESTS VALUES(921,'Frees','Jason','3204896123','716KAT','MN')
INSERT INTO GUESTS VALUES(57,'Samps','Sarah','7635674895','943TOA','FL')
INSERT INTO GUESTS VALUES(681,'Newell','Tony','2197514896','641TOD','MA')

INSERT INTO ROOMS VALUES(101,'Queen','0')
INSERT INTO ROOMS VALUES(102,'King','0')
INSERT INTO ROOMS VALUES(103,'King','0')
INSERT INTO ROOMS VALUES(104,'Queen','0')
INSERT INTO ROOMS VALUES(105,'King','0')
INSERT INTO ROOMS VALUES(106,'King','1')
INSERT INTO ROOMS VALUES(201,'Queen','1')
INSERT INTO ROOMS VALUES(202,'Queen','0')
INSERT INTO ROOMS VALUES(203,'Queen','0')
INSERT INTO ROOMS VALUES(204,'King','1')

INSERT INTO RESERVATION VALUES(11,101,50,'12/01/2016','12/17/2016')
INSERT INTO RESERVATION VALUES(21,102,72,'11/30/2016','12/20/2016')
INSERT INTO RESERVATION VALUES(31,103,921,'12/01/2016','12/10/2016')
INSERT INTO RESERVATION VALUES(41,104,57,'12/01/2016','12/10/2016')
INSERT INTO RESERVATION VALUES(51,105,681,'12/10/2016','12/22/2016')

[Kristen]

GUESTS Secondary Indexes on LNAME and PHONE perhaps?

RESERVATION will need some indexes too. It might be that RESERVATION_ID is not the best Clustered Index (so might need a NON-Clustered PKey)

RESERVATION table will get large - maybe you will have a RESERVATION_HISTORY table into which you "move" rows from RESERVATION table once the RESERVATION_END date has passed (or once it has passed by X-months so as to keep recent-history in the main table).

I'm probably over thinking the problem though ... I'm well known for that!

Generally speaking I would not give a BIT column the name "ROOM_STATUS". "Status" to me implies a number of different possible values, which might be added to in future. A BIT column is a Yes/No thing, so I would call the column either "IsDirty" or "IsClean" - depending on what you want the 1/true value to infer.

You have Plural for GUESTS and ROOMS but singular for RESERVATION. I suggest you settle on one or the other and then remain 100% consistent. My recommendation is to use Singular - I suggest you have a Google and see what you think. But ask two DBAs and you'll get three opinions ...

If you change them change the names for FKeys etc. to match.

@kldr1101

ROOM_SIZE varchar(5)

Do you need to enforce the values stored in this column some how? Similarly for GUEST_LICENSEPLATE_STATE I suppose.

[ScottPletcher]

HOUSEKEEPING_STATUS still needs to be in the ROOMS table, just not in the RESERVATION(?) table.

BE CONSISTENT with table names, either all singular or all plural. Mixing them makes it more difficult to work with the system later. So: GUESTS & RESERVERATIONS & ROOMS or GUEST & RESERVATION & ROOM.

[kldr1101]

Thanks for your help everyone, I have changed to have all the tables as singular rather than some plural and some singular. That makes sense to stay consistent.

I added a few views and will be adding some procedures, I also think I will add at least one control flow, function, and a trigger. I'll try to post some code later today with some more stuff.

Your help has been VERY MUCH appreciated so far.

Also, this is just an exercise to help me get into a Database class for college, if I can create a simple database and show that I can skip over the "intro class" (the next class I take will go over some basics, but it will be a faster pace class) So I won't have to worry about long term items for this database as it is just an exercise. Thanks!

[kldr1101]

Here is my current code:

CREATE DATABASE HOTELHOUSEKEEPING

GO

USE HOTELHOUSEKEEPING

CREATE TABLE GUEST
(
GUEST_ID int NOT NULL,
GUEST_LNAME varchar(15) NOT NULL,
GUEST_FNAME varchar(15) NOT NULL,
GUEST_PHONE varchar(10) NOT NULL,
GUEST_LICENSEPLATE varchar(10) NULL,
GUEST_LICENSEPLATE_STATE varchar(6) NULL,

CONSTRAINT "PK_GUEST" PRIMARY KEY CLUSTERED
("GUEST_ID"),
);

CREATE TABLE RESERVATION
(
RESERVATION_ID int NOT NULL,
ROOM_NUM int NOT NULL,
GUEST_ID int NOT NULL,
RESERVATION_START datetime NOT NULL,
RESERVATION_END datetime NOT NULL,
CONSTRAINT "PK_RESERVATION" PRIMARY KEY CLUSTERED
("RESERVATION_ID"),
);

CREATE TABLE ROOM
(
ROOM_NUM int NOT NULL,
ROOM_SIZE varchar(5) NOT NULL,
ROOM_STATUS bit NOT NULL,
CONSTRAINT "PK_ROOM" PRIMARY KEY CLUSTERED
("ROOM_NUM"),
);

ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_ROOM" FOREIGN KEY
("ROOM_NUM") REFERENCES "dbo"."ROOM" ("ROOM_NUM");

ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_GUEST" FOREIGN KEY
("GUEST_ID") REFERENCES "dbo"."GUEST" ("GUEST_ID");

INSERT INTO GUEST VALUES(50,'Klemz','Drew','7635492145','946GBA','MN')
INSERT INTO GUEST VALUES(72,'Johnson','Bill','4125486321','435NAT','WI')
INSERT INTO GUEST VALUES(921,'Frees','Jason','3204896123','716KAT','MN')
INSERT INTO GUEST VALUES(57,'Samps','Sarah','7635674895','943TOA','FL')
INSERT INTO GUEST VALUES(681,'Newell','Tony','2197514896','641TOD','MA')

INSERT INTO ROOM VALUES(101,'Queen','0')
INSERT INTO ROOM VALUES(102,'King','0')
INSERT INTO ROOM VALUES(103,'King','0')
INSERT INTO ROOM VALUES(104,'Queen','0')
INSERT INTO ROOM VALUES(105,'King','0')
INSERT INTO ROOM VALUES(106,'King','1')
INSERT INTO ROOM VALUES(201,'Queen','1')
INSERT INTO ROOM VALUES(202,'Queen','0')
INSERT INTO ROOM VALUES(203,'Queen','0')
INSERT INTO ROOM VALUES(204,'King','1')
INSERT INTO ROOM VALUES(205,'Queen','0')
INSERT INTO ROOM VALUES(206,'Queen','0')
INSERT INTO ROOM VALUES(301,'Suite','0')
INSERT INTO ROOM VALUES(302,'Suite','0')
INSERT INTO ROOM VALUES(303,'Suite','0')
INSERT INTO ROOM VALUES(304,'Suite','0')
INSERT INTO ROOM VALUES(305,'Suite','1')
INSERT INTO ROOM VALUES(306,'Suite','1')

INSERT INTO RESERVATION VALUES(11,101,50,'12/01/2016','12/17/2016')
INSERT INTO RESERVATION VALUES(21,102,72,'11/30/2016','12/20/2016')
INSERT INTO RESERVATION VALUES(31,103,921,'12/01/2016','12/10/2016')
INSERT INTO RESERVATION VALUES(41,104,57,'12/01/2016','12/10/2016')
INSERT INTO RESERVATION VALUES(51,105,681,'12/10/2016','12/22/2016')

CREATE VIEW ALL_ROOMS_VIEW AS
SELECT ROOM_NUM, ROOM_STATUS, ROOM_SIZE
FROM ROOMS;
--THIS VIEW SHOWS ALL THE ROOMS

CREATE VIEW CURRENT_GUESTS_VIEW AS
SELECT GUEST_LNAME, GUEST_FNAME, ROOM_NUM, RESERVATION_START, RESERVATION_END
FROM RESERVATION, GUESTS;
--THIS VIEW SHOWS WHO IS IN WHAT ROOM, AND HOW LONG

CREATE PROCEDURE prc_Add_Guest
@GUEST_ID
@GUEST_LNAME
@GUEST_FNAME
@GUEST_PHONE
@GUEST_LICENSEPLATE
@GUEST_LICENSEPLATE_STATE
-- this procedure will add a person to the guest table

CREATE PROCEDURE prc_Add_Reservation
@RESERVATION_ID
@ROOM_NUM
@GUEST_ID
@RESERVATION_START
@RESERVATION_END
--this procedure will add a reservation into the reservation table

Let me know if you guys have an ideas for useful control flows, functions, or triggers I should attempt/use for this database.

[kldr1101]

Here are my updated procedures:

CREATE PROCEDURE prc_Add_Guest
@GUEST_ID int,
@GUEST_LNAME varchar(15),
@GUEST_FNAME varchar(15),
@GUEST_PHONE varchar(10),
@GUEST_LICENSEPLATE varchar(10),
@GUEST_LICENSEPLATE_STATE char(6)
AS
INSERT INTO GUEST VALUES (@GUEST_ID, @GUEST_LNAME, @GUEST_FNAME, @GUEST_PHONE, @GUEST_LICENSEPLATE, @GUEST_LICENSEPLATE_STATE)
-- this procedure will add a person to the guest table

CREATE PROCEDURE prc_Add_Reservation
@RESERVATION_ID int,
@ROOM_NUM int,
@GUEST_ID int,
@RESERVATION_START datetime,
@RESERVATION_END datetime
AS
INSERT INTO RESERVATION VALUES (@RESERVATION_ID, @ROOM_NUM, @GUEST_ID, @RESERATION_START, @RESERVATION_END)
--this procedure will add a reservation into the reservation table

CREATE PROCEDURE prc_Delete_Reservation
@RESERVATION_ID int,
@ROOM_NUM int,
@GUEST_ID int,
@RESERVATION_START datetime,
@RESERVATION_END datetime
AS
DELETE FROM RESERVATION
WHERE RESERVATION_ID=@RESERVATION_ID
--This procedure will delete a reservation based on typing the reservation ID in.

[Kristen]

@kldr1101

GUEST_ID int NOT NULL,
GUEST_LNAME varchar(15) NOT NULL,
GUEST_FNAME varchar(15) NOT NULL,
GUEST_PHONE varchar(10) NOT NULL,
GUEST_LICENSEPLATE varchar(10) NULL,
GUEST_LICENSEPLATE_STATE varchar(6) NULL,

If you are going to have a Prefix on column names (indicating table) I would do it for all columns, on all tables. Most people don't like that naming convention, as it happens I do (except that I use a unique mnemonic shortcut for the prefix, and not the whole table name, just to save typing and prevent column names becoming very long).

For the columns in other tables use the prefix for that table, and for the joined table, where relevant

@kldr1101

RESERVATION_ID int NOT NULL,
ROOM_NUM int NOT NULL,
GUEST_ID int NOT NULL,
RESERVATION_START datetime NOT NULL,
RESERVATION_END datetime NOT NULL,

So for me that would be RESERVATION_ROOM_NUM which IMO has benefit because the column name. on its own, immediately tells me that the association is between RESERVATION and ROOM. Ditto for RESERVATION_GUEST_ID.

If I have a JOIN between RESERVATION and GUEST then if I accidentally use a column name that is not related to RESERVATION or GUEST it will be obvious just by looking at it, this prevents errors which, IMHO, can be very hard to spot - particularly in test data when every table has test data with ID's from 1 to 10 (and probably no more!!)

This immediately looks wrong to me because I've used the wrong associated columns, but if I did do this the code would probably work, it would give me unintended results though.

SELECT Col1, Col2, ...
FROM RESERVATION
    JOIN GUEST
        ON GUEST_ID = RESERVATION_ROOM_NUM
    JOIN ROOM
        ON ROOM_NUM = RESERVATION_GUEST_ID

@kldr1101

CREATE VIEW ALL_ROOMS_VIEW
AS
SELECT ROOM_NUM, ROOM_STATUS, ROOM_SIZE
FROM ROOMS; --THIS VIEW SHOWS ALL THE ROOMS

Not sure this does anything useful? You've got the plural name for [ROOMS] there, and also in the VIEW name. Personally I would never add a "_VIEW" suffix to a view name (nor a "TBL" one to a table).

Here's an scenario:

You have a table called MyTable. After some time you decide that you want to change some of the column names, and to split the table into two tables. So you create two new tables MyTable_V2 and MySubTable and you migrate the data from the original MyTable and then DROP that original table.

To save having to change all your existing code you create a view called "MyTable" which mimics the original single-table and column names of the original table. In order that the new view works with the existing code, and you have complete backward compatibility, you cannot call this "MyTable_VIEW"

@kldr1101

CREATE VIEW CURRENT_GUESTS_VIEW
AS
SELECT GUEST_LNAME,
GUEST_FNAME, ROOM_NUM,
RESERVATION_START,
RESERVATION_END
FROM RESERVATION, GUESTS;
--THIS VIEW SHOWS WHO IS IN WHAT ROOM, AND HOW LONG

"FROM RESERVATION, GUESTS" will make a Cartesian JOIN of every single row in RESERVATION joined to every single row in GUEST (watch those plurals again). You need to do this:

FROM RESERVATION AS R
    JOIN GUEST AS G
        ON G.GUEST_ID = R.GUEST_ID

@kldr1101

CREATE PROCEDURE prc_Add_Guest

I suggest you consider / research whether to have Verb-Noun or Noun-Verb for your procedure names. I prefer Noun-Verb - so [prc_GUEST_Add] and [prc_RESERVATION_Add] I recommend that you capitalise the Table Name part exactly the same as the table itself. Capitalise ADD too if you like.

Reason for this is because

prc_GUEST_Create
prc_GUEST_Retrieve
prc_GUEST_Update
prc_GUEST_Delete
...
prc_RESERVATION_Create
prc_RESERVATION_Retrieve
prc_RESERVATION_Update
prc_RESERVATION_Delete

will list in alphabetical order, by "table", whereas if you have Verb-Noun you will get all the "Add" ones grouped together.

In my example I have used the suffixes which correspond to the acronym "CRUD" - these are the Create, Retrieve/Read, Update, Delete functions and, generally speaking, an application need those [as a minimum] for every table in the database [to allow the records to be maintained]. You don't need to use those suffix names - "Add" is fine as far as I am concerned; for every table in our applications we have SProcs for Save (which does both Create and Update), Get and Del

Either way: BE CONSISTENT in your naming conventions.

@kldr1101

functions

Best avoided ! They perform badly (but maybe you are meaning something other than user Defined Functions?)

@kldr1101

triggers

Not sure you need any. If you are going to use a Stored Procedure to, for example, Save a record then you can put any Validation / Business logic in that SProc. IF you plan to allow other processes to directly update your database tables then Triggers could be used instead/ in addition to enforce your Business Rules.

If you are looking for an excuse to have a trigger then on reason might be to store Audit data. Here we do NOT store the current record in our audit tables (it is in the actual table, and just doubles up the space requirement if we, also,store it in the Audit table), so we just store the "old/previous record" whenever a record is updated, or deleted.

We have Audit Tables (using the name of the original table with a suitable "_Audit" prefix / suffix, somesuch) with two additional, initial, columns for: Action (Update or Delete) and Date/Time of the change.

So in our Audit Triggers we just store the "previous" row, i.e. ALL of its columns, into the Audit table. Note that a trigger MUST be built to be able to handle multiple rows. A trigger in MS SQL is only called once per insert / update / delete, regardless of how many rows are effected. (I believe that Oracle, for example, calls its triggers for each row that is effected)