Help with table design

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

re: Help with table design

lorirobn wrote:[color=blue]
> 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
>[/color]

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.

--
Smartin

re: Help with table design

Hi Smartin,

Thanks for your reply.
I forgot to mention that I do have an Item Table in place, so I have two
of your suggestions in my design structure (Rooms Table and Item Table).
Your Room Usage table is a good idea; I think I will incorporate this
into my Room Table by using a RoomActive indicator, with dates
associated with it, instead of creating a new table. I will know which
the active use is for the room, and that will work fine.

It's the RoomItems design that's tricky. I totally agree with you about
the need for normalization here, and the need for a table like this. I
actually wanted to do that from the start. However, here's the tricky
part. If each RoomItem contains the room that the item is in, I am
afraid the table will become inaccurate. My users will not track an
item. So let's say RoomItems table starts out with 600 lamps, wtih each
lamp assigned to a room number. Lamps may get switched around, by
household or maintenance or whomever, without the table getting updated.
So within a year, what becomes of the table? Even if items are
inventoried at regular intervals, how will they know what became of the
pink lamp in room 200 if it is not there anymore? I can't get past this
scenario of items moving around. If items were permanently in a room,
that would make it a lot easier.

Any thoughts?

Thanks... appreciate your suggestions,
Lori



*** Sent via Developersdex http://www.developersdex.com ***

re: Help with table design

Lorirobn wrote:[color=blue]
> Hi Smartin,
>
> Thanks for your reply.
> I forgot to mention that I do have an Item Table in place, so I have two
> of your suggestions in my design structure (Rooms Table and Item Table).
> Your Room Usage table is a good idea; I think I will incorporate this
> into my Room Table by using a RoomActive indicator, with dates
> associated with it, instead of creating a new table. I will know which
> the active use is for the room, and that will work fine.[/color]

Then you may not really need the RoomActive indicator. You might could
just use the date as implicit signal that the room is active.
[color=blue]
> It's the RoomItems design that's tricky. I totally agree with you about
> the need for normalization here, and the need for a table like this. I
> actually wanted to do that from the start. However, here's the tricky
> part. If each RoomItem contains the room that the item is in, I am
> afraid the table will become inaccurate. My users will not track an
> item. So let's say RoomItems table starts out with 600 lamps, wtih each
> lamp assigned to a room number. Lamps may get switched around, by
> household or maintenance or whomever, without the table getting updated.
> So within a year, what becomes of the table? Even if items are
> inventoried at regular intervals, how will they know what became of the
> pink lamp in room 200 if it is not there anymore? I can't get past this
> scenario of items moving around. If items were permanently in a room,
> that would make it a lot easier.
>
> Any thoughts?
>
> Thanks... appreciate your suggestions,
> Lori[/color]

My thought was you could track the condition of items ambiguously, so
even though you may not know the pink lamp was in poor condition, you
would know that a lamp (somewhere) was in poor condition and plan to
refresh your inventory accordingly.

The RoomItems table was not meant to place a specific lamp in a specific
room, it merely says there is a lamp in a specific room. I was assuming
a particular room configuration would infer a known group of items.

Hope this helps!
--
Smartin

re: Help with table design

Lorirobn wrote:
[color=blue]
> Your Room Usage table is a good idea; I think I will incorporate this
> into my Room Table by using a RoomActive indicator, with dates
> associated with it, instead of creating a new table. I will know which
> the active use is for the room, and that will work fine.[/color]

I really have no idea what you are actually trying to accomplish
with what little information you have posted, but one of the benefits of
a seperate 'Room Usage' table would be the availability of historical
data. You would be able to tell what your rooms were most often used
for, what they were used for during certain times of the year and other
possibly useful information along those lines.