Help with table design

[lorirobn]

Hello,

I'd be curious to hear other thoughts on my database and table design.

My database is for 'space use' in a lodging facility - will hold all
spaces, like rooms (lodging rooms, dining areas, public areas),
grounds, bathrooms, hallways, etc. User would like to keep track of
all spaces as well as items in them, and the condition of items (ie:
beds, so he can budget when it's time to replace them). He does not
want to track specific items, with tags or codes or anything like that.
Just inventory a room, know the items in it, and their condition.

I set up my database to have a main table called RoomHeader. This has
all the rooms in the building, with primary key of Room Number. I set
up another table called GuestRoom, with one-to-one relationship with
RoomHeader, also primary key of Room Number. This has all rooms that
guests sleep in. This table contains fields for all parts of a guest
room: door, carpet, paint, chair, closet, and many more. In addition,
for each item, I have a 'Condition' field (so I have DoorCond,
CarpetCond, PaintCond, ChairCond, etc). Condition is kept as a number
from 1 to 5. For Beds and Windows, I set up 2 additional tables.
tblBed has PK of RoomNumber + BedNumber. tblWindow has PK of
RoomNumber + WindowNumber. I did this so there could be an infinte
number of beds for a room (they have dorms with 25 beds), and windows
for a room. (I have not set up any other spaces yet besides Guest
Lodging, so as to get this piece finished fast).

I am now questioning my design. Is it ok to have all these inventory
fields plus their conditions? The field list will only grow with
additional space types. Is it ok to have RoomHeader separate from
GuestRoom; should they have been one table? I have just found out
that a GuestRoom can temporarily become an office or another room type.
Now I have to figure out how to disable the record that's on the guest
room table while keeping track of it on another table, and it's feeling
too complicated. Would there have been a better way to keep track of
condition? One thing that stymied me is the possibility of endless
amounts of items - for example, lamps in a room. I made lamp1, lamp2,
lamp3, lamp4 fields, but wasn't sure if that was the best way to go.

I am used to working on a team and throwing ideas back and forth... but
for this I am working solo. So I would appreciate any other
ideas/suggestions/support.

Thanks in advance,
Lori

 

[Smartin]

Hello,

I'd be curious to hear other thoughts on my database and table design.

My database is for 'space use' in a lodging facility - will hold all
spaces, like rooms (lodging rooms, dining areas, public areas),
grounds, bathrooms, hallways, etc. User would like to keep track of
all spaces as well as items in them, and the condition of items (ie:
beds, so he can budget when it's time to replace them). He does not
want to track specific items, with tags or codes or anything like that.
Just inventory a room, know the items in it, and their condition.

I set up my database to have a main table called RoomHeader. This has
all the rooms in the building, with primary key of Room Number. I set
up another table called GuestRoom, with one-to-one relationship with
RoomHeader, also primary key of Room Number. This has all rooms that
guests sleep in. This table contains fields for all parts of a guest
room: door, carpet, paint, chair, closet, and many more. In addition,
for each item, I have a 'Condition' field (so I have DoorCond,
CarpetCond, PaintCond, ChairCond, etc). Condition is kept as a number
from 1 to 5. For Beds and Windows, I set up 2 additional tables.
tblBed has PK of RoomNumber + BedNumber. tblWindow has PK of
RoomNumber + WindowNumber. I did this so there could be an infinte
number of beds for a room (they have dorms with 25 beds), and windows
for a room. (I have not set up any other spaces yet besides Guest
Lodging, so as to get this piece finished fast).

I am now questioning my design. Is it ok to have all these inventory
fields plus their conditions? The field list will only grow with
additional space types. Is it ok to have RoomHeader separate from
GuestRoom; should they have been one table? I have just found out
that a GuestRoom can temporarily become an office or another room type.
Now I have to figure out how to disable the record that's on the guest
room table while keeping track of it on another table, and it's feeling
too complicated. Would there have been a better way to keep track of
condition? One thing that stymied me is the possibility of endless
amounts of items - for example, lamps in a room. I made lamp1, lamp2,
lamp3, lamp4 fields, but wasn't sure if that was the best way to go.

I am used to working on a team and throwing ideas back and forth... but
for this I am working solo. So I would appreciate any other
ideas/suggestions/support.

Thanks in advance,
Lori

lamp1, lamp2, lamp3, etc. is a signal you need to normalize the data
structure. It seems you need separate room and inventory tables. Since
each inventory item is not tracked explicitly, you have a many-many
relationship between these entities, which means you need a table
joining these items. Additionally, you want to track the condition of
items over time, so you need some kind of usage table indicating when
the room <--> items are in use. You also have room purposes changing
over time, which suggests you need another joining table to determine
the use of the room on a particular day.

So, your tables are filling out like

Rooms
-----
ID
RoomNumber, etc.

Items
-----
ID
ItemDesc
ItemCost, etc.

RoomUsage
-------------
ID
Rooms.ID
UsageDate
UsageDesc

RoomItems
---------
ID
RoomUsage.ID
Items.ID
ConditionOnStart
ConditionOnExit


Now you can examine what items were in a room on a particular day, the
room's classification on that day, and the condition of the items on
each assignment.