Hi Eric,

In my opinion option 2 is the only way to go... I would not even consider option 1.

Do you use any modeling tools for designing database? Check out my recent thread about Modeling tools. I have to admit, I only used Visio so far...

>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