Duplicating data (normalisation?) (1 Viewer)

[David8]

As a beginner I am not as familiar as most here with normalisation.

Consider a Buildings table lending a foreign key to a Rooms table that in turn lends a room foreign key to an Equipment table. Building>Room>Equipment.

I imagine that re-recording the 'building' characteristic in a seperate field on the equipment table violates normalisation, because the building data automatically follows from the room specificied for each item of equipment via the relationships. If this is a normalisation issue, then which aspect is violated?

Would this (re-recording the building data against each item of equipment)ever be considered acceptable?

For example I want a datasheet view form to show the field building as well as room and to allow the entry of building data to narrow down a subsequent room combo box. I cannot use an unbound building field otherwise the building field will not display correctly in datasheet view.

I suppose I could have a single two column location form field that showed both building and room, but this does not achieve the cascade combo box effect I want. Really I only want the Equipment table to record RoomFK for location data. This is all the location data that is needed (from the equipment table perspective), but seems to make it impossible for me to design a datasheet view form that does what I want.

 

[dportas]

I imagine that re-recording the 'building' characteristic in a seperate field on the equipment table violates normalisation, because the building data automatically follows from the room specificied for each item of equipment via the relationships. If this is a normalisation issue, then which aspect is violated?

Sounds like a non-key dependency to me (room->building) which would violate Boyce Codd Normal Form.

Sound database design has nothing to do with user interface design. Get the data model right and then concern yourself with how to display it on a form.

 

[gemma-the-husky]

if you have a relationship

building ---> room ----> equipment

then the point is that given an equipment, you can uniquely identify the room, and the building, and thereby derive any attributes from those fields

you do this with a simple query.

so there is absolutely no need to store any of the attributes relating to the building, with the equipment. the building is nothing to do with the equipment

this is quite straightforward once you get used to it - but as you say, you are just starting out. keep experimenting.

 

[David8]

I was kind of hoping for an NF1 or NF2 answer on my first point.

On the other point, I may need to repeat myself, please read carefully:

For example I want a datasheet view form to show the field building as well as room and to allow the entry of building data to narrow down a subsequent room combo box [thereby a cascade combo box]. I cannot use an unbound building field OTHERWISE THE BUILDING FIELD WILL NOT DISPLAY CORRECTLY IN DATASHEET VIEW [of the form].

I suppose I could have a single two column location form field that showed both building and room, but this does not achieve THE CASCADE COMBO BOX EFFECT I WANT. Really I only want the Equipment table to record RoomFK for location data. This is all the location data that is needed (from the equipment table perspective), but seems to make it impossible for me to design a datasheet view form that does what I want.

I'm gratefull for any answers, I just feel the two answers I've got so far didn't really engage with the specifics of what I'd said in this particular section.

Exactly which simple query will allow the first part of the cascade lookup (Building (then narrowing down to rooms) in the form given my requirements? That's given my other requirements. I can, now, do a simple cascade lookup, but it involves not being able to do other things with the form.

I repeat, in the Form, Building cannot be bound. If I am wrong, then what could it be bound to? And if it is not bound, but worked out based on a query that gets its information on the choice in the room field, then this is mutually impossible, because building is supposed to be chosen FIRST in the cascade lookup - BEFORE room not after.

The only way round this I can see at the moment is to bind the building form field to a second recording of building in the equipment table.

Remember please that I am focussed on DATASHEET VIEW forms here. I say that because most of the forms I see in examples are not.

If you can please, give a specific answer about how I am going to get out of what I'm seeing at the moment as a connundrum, OR exactly why it is not a connundrum after all. Thanks.

 

[David8]

I've probably made a mistake. I've gone and titled this thread with the word normalisation.

I did want to touch on that, but I suppose its really about how I can create my form to do what I want, and whether it will be necessary to move away from ideal database design to achieve that behaviour for the form.

 

[David8]

Bump. Any chance of answer on this?

I've now tried violating normalisation by creating a Building field in my main table (not necessary data-wise because "building" is implied by the "room" field that's already there).

This results in the Building FORM field displaying correctly in datasheet view, AND the cascade lookup works correctly when entering data. BUT it doesn't work correctly when reviewing data. If I last entered "Building A" then the only Room records that will show up in the Rooms form field are those that belong in "Building A". All the other rooms are not shown in the datasheet (I DO NOT MEAN THE LOOKUP I mean the datasheet, I'm talking about reviewing data not entering it), until I enter another building eg "Building B" and then only those room records that correspond to "Building B" show up.

How do I get cascade lookups to work with datasheet view? There must be some code I'm missing. This is so frustrating.

 

[The_Doc_Man]

Normalization is a wonderful thing, but sometimes causes headaches until you get accustomed to it.

You need a table of buildings. One field will be the building prime key (PK).

You need a table of rooms. One field will be the room PK and one field will be a foreign key (FK) that points back to the building record (that has the matching PK).

You need a table of equipment. One field will be the equipment PK and one field will be the FK to the room that has the matching PK.

To see everything at once you need a query that does a one/many join from building to room and another query that does a one/many join from room to equipment. Then one more query that joins the data from the two lower-level queries so that the recordset you get from that table holds everything (virtually). Put THAT query in datasheet view. Everything should show up.

 

[gemma-the-husky]

normally what you are looking for is achieved by a form/subform.

have a form for buildings

a subform for rooms

a subform for equipment

access can link these automatically

so as you navigate (with navigation buttons at the bottom of the main form) to a different building (and/or room), the rooms (for that building) will automatically update and the equipment(for that room) will also do os.

All this without any code AT ALL. The equipment subform can be a datasheet.


alternatively, you can put all this into a single form (via a query), and include the building and room data on that form. You can then interactively use sorts and filters to select the room/building you want.