General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
I have a process that I'm automating. It's a dorm room check in/out process. When students move in or out of a room they fill out a form that indicates the status of the room, ex, bed, bookcase, ceiling, closet etc... Each item is indicated by a poor, good, new, or needs repair status and there is a space to enter comments on each item. Also, each check in/out has to be related to a studentID.
How could I construct a solid db for this process. I have a couple different ideas so I need a second or third opinion.
RCI stands for room condition inventory.
Option 1
rci
--------------------------
rciID int identity
studentID int //FK to students.studentID
rciType int //(checkin or checkout)
bedframe int or char(1)
bedmattress int or char(1)
badmattresspad int or char(1)
bookcase int or char(1)
ceiling int or char(1)
closet int or char(1)
...
bedframecomments varchar(4000)
bedmattresscomments varchar(4000)
badmattresspadcommentsvarchar(4000)
bookcasecomments varchar(4000)
ceilingcomments varchar(4000)
closetcomments varchar(4000)
...
//would not need if i use char in the rci table because I could just store [P, G, N, R] for each type of status
rciStatusOptions - store an id for each status type, poor, new, good, needs repair
------------------------------
rciStatusID int identity
rciStatusDesc varchar(50)
The major problem with this is that the rci table will have 3 + 38 x 2 = 79 columns! I dont want to do this. Another option is.
Option 2 is a little more complicated but it is more normalized
rci
------------------
rciID int identity
studentID int //FK to students.studentID
rciType int //checkin or checkout
rciItemStatus -- contains a row for each item for each student rows = # of items on the form X number of students = alot of rows (approx 500K)
------------------
rciItemID int identity
rciID int //fk to rci.rciID
rciItemID int /fk to rciItems.rciItemID
status int or char(1)
comments varchar(4000)
rciItems -- Contais a record for each item that appears on the form. about 30 rows
------------------
rciItemID int identity
itemDescription varchar(100)
//would not need if i use char in the rciItemStatus table because I could just store [P, G, N, R] for each type of status
rciStatusOptions - store an id for each status type, like poor, new, good, needs repair
------------------------------
rciStatusID int identity
rciStatusDesc varchar(50)
I'm already leaning toward option 2, but if anyone has a better idea let me know please. TIA - Eric
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only