Database design advise sought!

[shrndegruv]

Hi all

I am creating an apartment unit inspection DB. Each unit has a number of rooms, and each room has a number of condition. So I am thinking of Table design such as:

Apartment Unit <-- Kitchen
^--------- Bedroom
^--------- Bathroom
^--------- Bedroom (variable)

so each of the rows in the tables in the second column matches to one row in the ApartmentUnit table. Now every room has conditions, like WindowConditions, PaintConditions, that have to be entered, so Im thinking every row in those 2 tables will match with a row in one of the tables I listed in the second column.

Sound reasonable?

Where it gets complicated is, how do I represent all of this in a form. When I create a unit via form, I already have to create a variable number of bedrooms which I do with VB. How do I handle the conditions. Ideally the user will not realize that rows in tables are being created when they enter info.

What I am thinking is that I will just create a Window condition for each of Kitchen, Bedroom, Bathroom as they are creaeted (there is only one window condition for each room, not one for each window).

Im just bouncing my idea off this board to make sure Im doing it as simply as possible.

thanx

 

[M8KWR]

I would have 3 tables.

The first table containing you Unit number.

The second contain the room info, i.e Kitchen,

then the third contain the information about the rooms i.e Kitchen windows.

This way when a user enters a new Unit Number, then they can enter multiple lines in the 2nd tables, and then multiple lines of each value in the 2nd tables to the 3rd tables.

Hope this makes sense, if not let me know...

 

[shrndegruv]

Im a bit unsure of what you mean.

Each room has room specific info that goes in the appropriate table.

For common data across the rooms, I wanted to extract that out, so I create tables like WindowCondition, PaintCondition, etc...

The problem with that is when I go to make my entry form, how do I put the WindowCondition fields in the form. Each room has one one, so I need to be able to enter info from one form that is going to go to multiple rows in one table.

To clarify -- I have one big form for each unit; this form enters data from multiple rooms within the unit -- but for the condition info, the form has to be able to enter data in multiple rows...

 

[The_Doc_Man]

In the mininum case, this seems to be at least 3 tables. The reason is that you want to report with three sections:

Apartment #
Room
Inspection item

To do 3 breaks correctly you need 3 tables.

(You CAN cheat if you wish, though, and make it 2 tables... but you'll pay the price in the long run for flexibility.)

You can define the relationships this way. (I'm assuming you have enough properties - or PLAN to have enough properties - that you could, in the future, have an overlap with apartment numbers.)

tblAptmt
fldAptmtID, Prime key, probably auto-number good enough.
fldAptmtNum, format depends on how you number your apartments
other apartment info

tblRoom
fldRoomID, Prime key, probably auto-number good enough.
fldAptmtID, foreign key to tblAptmt, number
fldRoomCode, what type of room is it (BDRM, BATH, KITC, LVNG, DEN, ...)
fldRoomNum, if you have BDRM #1 and #2, this is where you put the number

tblInspItem
fldInspID, Prime key, probably auto-number good enough.
fldRoomID, foreign key to tblRoom, number
fldInspDate, date, when it was inspected
fldItemType, what the inspection looks at (WNDW, SOFA, TBL, CHR, RUG, WALL, FLR, CLNG, etc.)
fldItemNum, room has WNDW #1 and WNDW #2, the number goes here
fldItemDesc, more descriptive info about the specific item
fldItemCond, the condition of the item after inspection

Relationships: Apartment (1/many) --> Room (1/many) --> Inspection Item

NOTE: The above design is NOT fully normalized. To do THAT, here is what I would do:

tblProperty
fldPropID, prime key, autonumber
fldAddress, etc.

to tblAptmt, I would add fldPropID as a foreign key. This is how you would handle the case of being so prosperous as to have two or more properties at the same time, potentially with the same apartment numbers for some items.

tblInspection
fldInspectionID, prime key, possibly autonumber
fldAptmtID, for. key to apartment table
fldRoomID, for. key to room table
fldInspDate, date of inspection
fldResult, overall result of inspection (text or a number or whatever)
fldRecent, yes/no (watch for this explanation later)

tblInspectables
fldInspectableID, prime key,
fldAptmtID, for. key to apartment table
fldRoomID, for. key to room
fldItemType, as described above in the tblInspItem
fldItemNum, as above
fldItemDesc, as above

THEN I would split out the inspection results table to look like this:

tblInspItem
fldIIID, (Inspection Item ID) Prime key, probably auto-number good enough.
fldInspectionID, for. key to tblInspection
fldInspectableID, for. key to tblInspectables
fldRoomID, foreign key to tblRoom, number
fldItemCond, the condition of the item after inspection

In THIS case, your relationships would look like this, being loose with the names:

Property (1/many) --> Apartment (1/many) --> Room (1/many) --> Inspectable

Inspection (1/many) --> InspItem BUT you get the checklist from the Inspectable table for the same room.

Given the likelihood of changing properties, apartments, rooms, and inspectables that often, you can set up a JOIN query for the purpose of making the checklist and report. You might need to make it a "relay" query because Access doesn't really like to define multiple joins in a single query without going to SQL to do it.

The nice part, though, is that as long as none of the queries are SUMMATION queries, you can update through the final query rather than going direct to the tables, because nothing there is ambiguous.

Now, that "Recent" flag in the Inspection table? Only one inspection per room is considered "recent" - all others are "not recent" - so you have some flag maintenance when you do an inspection.

You can probably get away with some wizard-generated forms for maintaining your properties, apartments, and rooms. You only have to update these when you add a new property anyway, so I wouldn't spend an awful lot of time on them.

The simple approach, though not normalized, might be adequate for getting started. However, keep in mind that to do it scrupulously right, you need to fully normalize as I showed you.

 

[shrndegruv]

Doc Man

thanx for the long response. I basically did what you said, with the difference that I have a table each for Kitchens, bedrooms, bathrooms, LivingRooms, as they all have their own inpection points that do not overlap. I then have condition tables for each item that all the rooms have in common, like windows and paint.

The relationship between unit and kitchen, bedrooms, bathrooms, etc are all 1 to 1. The relationship between the conditions to kitchen, bedroom, etc, are all many to 1. I could not come up with a unique foreign key, since each of kitch, bed, etc, have autonumber primary keys and their is overlap.

I was worried about generating forms when I had a join on all the 1 to 1 tables and then another for all the many to 1 tables, but subqueries will handle this, I think.

A little VB goes a long way in setup, because whenever I create a unit I can automatically create rows in all the tables dependent on the unit.

Originally I had all the window info in each of bed, bath, kitch, etc, which I knew was poor, and I paid for it when I tried to create a report. Taking the time to extract out the common data will go far for reports.

I think I am all set.

Thanx