Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Help with table design

Discussion in 'Information Technology' started by lorirobn, Nov 12, 2005.

Tags:
  1. lorirobn

    lorirobn
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Smartin

    Smartin
    Expand Collapse
    Guest

    lorirobn wrote:
    > 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.

    --
    Smartin
     

Share This Page