Opinions on database design - Rooms + Items in room

[lorirobn]

Hello,

I'd be curious to hear thoughts on my database and table design. I posted another question recently but this is for a different design question.

My database is for 'space use' in a lodging facility - will eventually hold all spaces, like rooms (lodging rooms, dining areas, public areas), grounds, bathrooms, hallways, etc. User would like to keep track of items in the space as well, and the condition of items, 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.

For the items, I have an Item Table, which contains all the different types of items and their description. The Room Table contains fields for all items in a room: door, carpet, paint, chair, closet, and many more, and each of these items is populated with data from the Item Table. For each item in the Room Table, I also have a 'Condition' field (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 infinite 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. Would there have been a better way to keep track of a room's items and their condition? What about the possibility of endless amounts of items - for example, lamps in a room. I made lamp1, lamp2,lamp3, lamp4 fields on the Room table, but not crazy about this solution. But, if I made a separate RoomItem table (Item ID + Room it is assigned to), I am afraid this would quickly become inaccurate, as user does not want to track items, and items do get switched around. So if a lamp is switched among rooms, the RoomItem table would be inaccurate. If items were permanently assigned to a room, I would prefer this design route.

Any suggestions?
Thanks in advance,
Lori

 

[jsteph]

I'd suggest having a Room-Item table. This would have a key of
Room#
ItemID
Sequenc#
Condition

This is an associative table, since you're not tracking individual 'Items', just 'Item Types'.

So, visualize a relationship with a Room table on the left with a Room ID, an ItemType table on the right with an ItemID, and in the middle--the Room-Item table, keyed on Room#,ItemID, and Sequence#.

The sequence # allows many of the same item-types per room, and each record has it's individual value for 'Condition'.
--Jim

 

[lorirobn]

Jim,
Room-Item Table is an excellent idea!
Question: how could I go about having the items organized on the room's data entry form? Currently, I have a RoomDetails form with 7 tabs, each tab being another item category in the room. For example, there's a Furniture Tab, with desk, table, chair, wardrobe. There's a Surfaces Tab, with carpet, paint, door. The way I have it now, "Desk" field on Room table is record source for "Desk" control on form. With your suggestion, my question is: how would I go about moving 'Desk' from Room-Item Table to a specific control on the form? Would ItemType table have to have the ItemType (ie: 'Desk') in addition to ItemID, so then I could move any 'Desks' for a room to the Desk controls on the form? Would all controls need to be in datasheet view, so that they could repeat for all sequence numbers for that type? (or, I could just do that for items that repeat).

Thanks,
Lori

 

[jsteph]

You need a form/subform.

But first, add a field Category to the Items table.

Use a query on the Rooms table as the main form's base. Create a query linking the 3 tables and use that as the subforms base. Link the subform to the mainform on RoomID.

Now, for each Room, the subform shows the items in that room, along with the condition, sequence number, category etc.

If you want the tabs, and don't want to list all items at once, say, sorted by category, you can still use tabs, or a better way might be to have a listbox, combobox, or option group showing the different categories.

For the source of this, use either a select distinct on the Items table's Category field, or create a seperate Category table. Then, on the selection of the category from the combo, list, or option group, you requery the subform based on that category--use Filter, or my preference is to alter the sql source of the subform with a Where clause on the selected category.
--Jim

 

[lorirobn]

Hi Jim,

I'm getting it, slowly, and liking this idea. It makes a lot of sense.

However, I'm still not clear on how I would lump items together on the room's form/subform, AND allow for 'endless repeats' of items. For example, if I want to lump together Nightstand, Dresser, Chair, and Couch on a Furnishings Tab, how can I do so and allow the space for say, two nightstands?

Currently, I do have these items on a Furnishings Tab.
It's like so:
Nightstand1: <drop-down cbo>
Nightstand2: <drop-down cbo>
Dresser: <drop-down cbo>
Each is a combo box with the Items Table as its source, by Category (which you suggested, but I happened to already have in place). Before each cbo is a label. The cbo has category (ie: 'nightstand') hardcoded into its source sql. If I leave leave it more open-ended and remove the hard-coded labels, and allow items to repeat, I don't know how I would control grouping such items together in my pre-defined way. Not sure I understand your suggestion on how to do so (display in sorted order?).

Sorry if I am missing something obvious. I think you are saying to have it sorted by Category, and then display the category and then the item for that room. So the only thing I am stuck on is having it displayed in my pre-arranged way.

Once again, thank you so much for your help.

Lori

 

[jsteph]

I'm not sure what you mean in your diagram with:
Nightstand1: <drop-down cbo>
Nightstand2: <drop-down cbo>
Dresser: <drop-down cbo>

My point about the combobox was for choosing which category to display on the form, since you may not want to clutter the form or have all items listed in a scrolling list.

So I'm not sure I understand the drowdown's you are showing. I wouldn't suggest a dropdown for the ItemID displayed on the form, it's too resource-intensive and there are other ways to accomplish the same thing.

You might want a dropdown to be used as a device to add a new item, but it'd be a stand-alone dropdown above the subform, for example. You could have the dropdown listing all the items and then on_Click you get the ItemID of that item, along with the RoomID of the currently displayed room, and then behind-the secenes via SQL you add a record to the RoomItem table.

For the Sequence# you'd either let it be an Autonumber (easier, but could be problematic), or you'd just use an Integer data type, and prior to any inserts you get the highest Sequence# for that item/room, then add1 (a better idea than autonumber, in my opinion).
--Jim

 

[lorirobn]

Hi Jim,

What I meant by my diagram of:
Nightstand1: <drop-down cbo>
Nightstand2: <drop-down cbo>
Dresser: <drop-down cbo>

was just my attempt to show a 'section' of my form. Those are just controls that would be on a typical tab (the Furnishings Tab, in this case). By '<drop-down cbo>' I meant that would be the combo-box control. So this represents six controls - 3 labels, 3 combo boxes. Hope that is clearer! I wish I could draw my current form here to show you!

Currently, when user clicks on the Nightstand combo box, he sees a list of all Items on the ItemsTable for category 'Nightstand'. For example: 'White Wicker; Wood with Shelf; etc...'. Because I have MANY combo-boxes, I guess it is very resource-intensive, as you say? (I am a newbie to Access, but am a programmer). You mention there are other ways to accomplish the same thing (displaying all Items for a given category). Would you be able to specify?

I do need to show all categories, rather than have the user choose which category he would like to display. I use the tabs to try and make it less cluttered.

I am still trying to understand how I could go about having repeating items (ie: 2 nightstands, 4 lamps) while also organizing items together on a tab and not in a sorted order?

I will attempt a 'replication' of a current tab on my form:

-------------------------------------------------------
[COLOR=green yellow]FURNISHINGS TAB [/color]
[purple]
Nightstand1: White Wicker
Nightstand2: Wood with Shelf
Dresser: White Wicker [/purple]

[COLOR=green yellow]ACCESSORIES TAB [/color]
[purple]
Lamp 1: Ceramic with white shade
Lamp 2: Ceramic with black shade
Lamp 3: _______ (blank)
Lamp 4: ________(blank)
Artwork 1: Monet with bird
Artwork 2: ________(blank)
[/purple]

I am trying to basically say 2 things: that I organize Items within tabs. And that for now, I define each Label and each combo box per Item, which is not the best way to allow for items that can repeat. So not sure how your suggestion(s) would work.
I hope this is clearer?!?!

Thanks once again,
Lori

 

[jsteph]

Here is a shot of a form I put together using some hastily done-up test data:

I did not use tabs, because I just put this together with the Wizard to show one way to look at the data. Notice the Category at the left in the subform, you could instead have a tab for each category, but in this example I just list them all in the lone subform.

Also note that listing the Room ID & Name in the subform is redundant.

At the top, the listbox shows all the items listed by category. There is code behind that which, upon dbl-click, it addes a record for that room and that item, getting the Max Seq# for that item/room, and adding 1. Then it requeries the subform.
--Jim

 

[BobRodes]

<I am now questioning my design.

Though I don't have much to add to the very thorough answers already given, I can chime in with a more theoretical answer, which will perhaps give a bit of additional background.

Your intuitive concern is well-founded, if theory is to be believed. Theory states that "two kernel entities" (i. e. "two different kinds of stuff") in many to many relationship will be related via an "associative entity", as jsteph has explained. In other words, one kind of stuff has many of the others associated with it, and the other kind of stuff also has many of the first associated with it. So, applying this to your case, a room can have many items, an item can be in many rooms. Many-to-many relationship. The way to relate these is with another entity each of whose records contains a definition of which item is associated with which room, and how. You will notice that this associative entity is in one-to-many relationship with each of the kernel entities it serves to relate.

So, to take this out of theory and into practice: you resolve tables that are in many-to-many relationship by deriving an associative table that is in one-to-many relationship with each.

Your intuitions clearly explain where the problem lies in trying to shoehorn this kind of architecture into a one to many structure. Your original structure has scalability problems (hard to add a new kind of item, for example), as you have surmised on your own.

Now, looking a little further down your posts, it seems to me that you also have another entity implied. Taking your example:

Nightstand1: White Wicker
Nightstand2: Wood with Shelf
Dresser: White Wicker

ACCESSORIES TAB

Lamp 1: Ceramic with white shade
Lamp 2: Ceramic with black shade
Lamp 3: _______ (blank)
Lamp 4: ________(blank)
Artwork 1: Monet with bird
Artwork 2: ________(blank)

The following are implied:
Kernel Entity 1: Rooms
Room number : Primary Key
Anything having to do with a single room.

Kernel Entity 2: Items
Item ID : Primary Key
Item Category ID : Foreign Key
Description
Condition
Anything else that has to do with a single item

Characteristic Entity: ItemCategories
Category ID : Primary Key
Category Name
Anything else having to do with an item category

Associative Entity: RoomsItems
RoomItem ID : Primary Key
Room ID : Foreign Key
Item ID : Foreign Key
Anything to do with a room item's relationship with a given room, e. g. Date placed

All right. Some words I haven't defined previously: characteristic entity is an entity that defines in some way another, usually kernel, entity. In this case, an item category defines an item in some way. A primary key is a value that uniquely identifies a record. No two records can have the same primary key value. A foreign key is a field whose value is a primary key value in another table; this is the mechanism by which relations are derived.

By the way, there are those that will say that the RoomsItems table should have a "compound primary" key of room id and item id. This is fine, too; I just prefer to do it the way I have outlined. Either way is fine.

While I could fumble together an Access application that implements this database, I prefer to do major database applications in VB, so I'm not to be considered an expert. However, I would call myself something of an expert in database design, and from this perspective, let me make clear that it is very important to get the underlying design clearly before attempting to implement it. (Of course, there will always be changes: alterations to business rules, miscommunications from the product stakeholders and so on, that will imply changes to the db design.) Consider the model that I have given as a beginning, and attempt to derive other entities of the three types described. Basically, a good database design avoids keeping data repetitively as much as possible. What this means is boil all your stuff down to one to many relationships, and one could say that that is the essence of good database design.

Good luck, and hope this helps,

Bob

 

[jsteph]

Here is an shot of what the relationship looks like:

The key thing to notice in the center or 'associative' table, is the SeqNum. Without it, while a Room could still have many items, an items could be in many rooms, you'd be stuck with only one of each kind of item per room.

With the seqNum, you can have as many of the same item ID per room--and each can have it's own Condition--and of course you can add as many other attributes to the center table to further describe each item.

Note that I threw in ItemCat as an afterthought, this may have it's own table joined to tblItems if you like.

This design solves your problem, but another way--which you'd said you didn't want to go that route--would be to have an Items table that lists every single individual item--which could be tracked, as you'd suggested, with barcodes. This would simplify the db design, but it would be a logistical headache for you to keep track of everything.
--Jim

 

[BobRodes]

I don't think I agree with your design, Jim. Here is the first of several questions: why can't you use SeqNum only to define the primary key in your associative table? And if you can, why do you have the other fields in your key?

Bob

 

[jsteph]

Normally, this associative table would just have the right and left side keys and that's it--no seqnum. That's there to allow mulitple same-items per room. I don't see how or why seqnum could be the key.

If you're getting at the point that one *could* use an autonumber or some manually generated value for *any* table instead of a compound-key, that's something I disagree with--I much prefer the intuitive compound key, even if it may go against someone's ideal of third-normal form or whatever or take a few more nanoseconds to process. It's easier on the designer, and that means a lot. Why confuse the developer and any future folks who have to maintain it by having a meaningless number as a key?

-Jim

 

[BobRodes]

<Normally, this associative table
What you are calling an associative entity, whether normal or otherwise, is in fact a characteristic entity.

<I much prefer the intuitive compound key
Again, both ways of doing it are fine. It doesn't "go against someone's ideal of third-normal form or whatever". (To verify this for yourself,

http://www.bkent.net/Doc/simple5.htm

is a concise explanation of the forms.) I prefer the other way, which is not an indightment of the way that you prefer. I personally find it harder (and in my experience, less maintainable) to have a field function both as a primary key and a foreign key. To do it your way, I would amend the RoomsItems table as follows, leaving the rest as is:

Associative Entity: RoomsItems
Room ID : Primary Key/Foreign Key
Item ID : Primary Key/Foreign Key
Anything to do with a room item's relationship with a given room, e. g. Date placed

As you can see, my structure still differs from yours. The point that I'm actually getting at is this: you don't need the seqNum field if you organize the tables in the fashion more accepted by theory. Furthermore the structure I've detailed is a more efficient way of allowing multiple same-items per room. The reason your way is less efficient (and, by the way, it is indeed a violation of the fifth normal form which states that a record type cannot be expressed in terms of multiple smaller record types) is because there is only one of a lot of items in each room, so you have to keep extra state about those items (a sequence number of "1") that is unnecessary to define its relationship with the room. And state is expensive, both in terms of time and space.

As for your apparent feeling that intuition is preferable to the normal form concept, I would point out that most established design patterns are to some extent counterintuitive: since they have been established over time, they are not the patterns that were first intuited. Furthermore, if their value had not been established in spite of that fact, they wouldn't exist. Ergo, intuition isn't superior to established theory. Nevertheless, it is indeed important to take them with a grain of salt. If not, there would be no concept of "denormalization," which of course there is.

What I take exception to in your design is this: one denormalizes or departs from formal structure in order to achieve practical efficiencies. Since your denormalization reduces efficiency (QED) it isn't based on the established reasons. I also assert that your design is less maintainable than mine, in that it is less consistent with established best practices and therefore needs to go through a process of "translation" in order to be understood by anyone else. (Why indeed confuse the developer?)

So, I assert that your design is both less efficient and less maintainable, having given what I consider to be strong reasons for both. I invite you to change your point of view or refute my assertions, in which case I shall change mine. Either of those failing, we shall agree to disagree and part friends, and leave it to lorirobn to do as she sees fit.

Best regards,

Bob

 

[jsteph]

Bob,
I agree with you that the sequence number is somewhat a kludge, it is what the poster wanted though--the Items table was not to have individual items--just a generic item, which would have to be repeated per room.

So, given the posters wish (against my better judgement and yours) to have that table remain the same I considered your suggestion:

Associative Entity: RoomsItems
   Room ID : Primary Key/Foreign Key
   Item ID : Primary Key/Foreign Key
   Anything to do with a room item's relationship with a given room, e. g. Date placed

...and I'm not seeing how you can have multiple same-items per room without some other component to the key of the associative table?
--Jim

 

[BobRodes]

Aha. Now we're connecting! Thank you for explaining. I didn't understand why you would come up with something like that, knowing what you know, and didn't see the line in question from the OP:

<He does not want to track specific items, with tags or codes or anything like that.

My response is that the fact that the stakeholder does not want to track specific items doesn't mean that you shouldn't create a table of specific items in a database!! You can present the data any way you want to. Just build views and queries and things for the user.

In a database design I see (at least) four processes:
1. Figure out what data you want to keep (identify kernel entities).
2. Derive a well-formed database structure.
3. Design a robust data entry process, with well-enforced referential integrity.
4. Create presentation artifacts.

These processes are loosely coupled. Don't let anything in number four cause you to compromise on number 2, because it isn't necessary.

Now, that said, you get situations where the stakeholder says things like "I want to be able to use Access to enter data in my tables, and I don't want to have to enter data in two different items tables or whatever to get what I want, and I don't want to spend any money." To which there is one simple response: "No."

All kidding aside, don't let a user's interface requirements get you to compromise on well-formed database design. You can present the data any way you want. In the end, they will always thank you for that if you hang tough.

To answer your question, you have multiple same-items per room because two "same items" in a room are simply two records in the items table with the same item category id. (These two items' ids will be in two records in the RoomsItems table, each of which will have the same RoomId.) The "sameness" or "nonsameness" is determined by evaluating a characteristic of an item (in this case what I called an item category), hence the name characteristic entity ("lookup table") for the ItemCategories table. Does that answer?

Bob

 

[jsteph]

Bob,
Yes it does. And believe me, I do spend a great deal of time when I do the design of the structure (#2)--I know I'll have to live with the design for a long time...plus I don't want to have to sheepishly explain or excuse-away some poor design when 5 years from now someone asks about it. It just takes one embarassing moment like that to make one stick to the 'do it right the first time' motto!
--Jim

 

[lorirobn]

It's been interesting reading your postings and opinions on my database! Thanks for the comments & advice... I never expected a discussion like this!

I happened to really like Jim's design and the sample form, and thought it would work really well for this database. But Bob's response has got me wondering.

To make sure I am following along correctly, the main difference between your two thoughts is sequence number on the RoomItems table? Bob does not like the use of sequence number on this table, while Jim suggested it to go along with my request? Ok, got that.

Bob writes [purple]<<the fact that the stakeholder does not want to track specific items doesn't mean that you shouldn't create a table of specific items in a database!!>>[/purple] I'm not sure I get this. I can have a table of specific items, but what happens when those specific items are associated with a room (RoomItems table)? I foresee items getting moved around, not being tracked, and a database with great design but inaccurate data. A lot of items are highly moveable (like pillows and cushions), while some of them are not (beds). (Btw, eventually my user may go to bar-codes, but that is a very long way away). Am I missing your thought or suggestion, Bob? Is your suggestion still as you originally wrote above, with the 4 tables?

Sorry if I am missing something.
And thank you both for your thoughtful advice on this!

Lorirobn

 

[BobRodes]

<It just takes one embarassing moment like that to make one stick to the 'do it right the first time' motto!

I'm sure anyone with experience has had more than one of those!

Lori, I think Jim and I both agree that putting the sequence number in the RoomItems table isn't the best idea from a purely architectural point of view. Once we figured out what was going on in each others' heads, we've been discussing the practical aspects of it. I wouldn't do it, period.

Yes, my suggestions regarding structure are still the same. A database with great design doesn't HAVE inaccurate data (well, knock on wood)--that's why it has a great design!

To address your concern, though: you do indeed need to consider how to get data in and out of your database. However, that is a matter that isn't precisely related to how you design your database, except in that it's easier to perform robust operations on a well-formed database.

Now. The fact that some items are more mobile than others is irrelevant: this fact doesn't justify adding complexity to your design, which you would certainly do if you had some "mobile" items that you handled differently from some "immobile" items.

So, the way I would associate an item with a new room is to delete a record from the RoomsItems table and add a new one. Now, you could simply change the Room number in the existing record for the given item, but I don't believe in altering key fields. It requires a lot more work to avoid duplicating keys at any part of the process than it does to delete a record and add a new one.

(Note that the process of deleting a record and adding a new one must be "atomic", meaning that you have to do both or neither. When the time comes to address that, you'll want to research transactions.)

Does this answer?

Bob

 

[lorirobn]

Yes, it helps. And it addresses the situation of a new room. My concern is more about existing rooms and items. What happens in the situation of Household members switching items around? The database never finds out. Data is never changed. RoomItems doesn't reflect the switch. ItemId's exist in the building but can't be tracked to a given room. So - not sure I understand your opinion on this, although I'm getting the feeling it's to make sure users do in fact track it??

Also, in deleting a record from the RoomItems table, as you mention, the way I see it, there would actually be 50-100 records deleted, because this is how many items there would be in one room.

Thanks,
Lori

 

[BobRodes]

<What happens in the situation of Household members switching items around? The database never finds out.

Can you recommend a design wherein the database WOULD find out?