Database design and relationships

1. I go along with Ken's recommendation to pre-populate your table with all the options. So, if you are going to ask the user to make a choice for each type of light fitting, then you would run an Append query appending all the light fitting options to the junction table for the customer and room. You can then test to see if any of those values are not filled in before you close the form.
2. What Ken said
3. Again, I agree with Ken. If you are "surveying" your customers at different times and expect them to complete the entire survey, then you would add a record to tblResponse with the date and then the ResponseID would become a FK for all the answers. But if you want customers to update only specific answers, then you can either add a modified date to the Answers table, or use an Audit trail. An Audit trail will create a record in a separate table that lists the table and recordID that was changed, the old and new values, the date/time it was changed and who changed it.

1.  Forcing a response to each possible question could be done by requiring some validation code to ensure that there is a row for each house/room/fitting in the table which model the relationship type between the three entity types.  This can't be done as part of the logical model, however, as a row must exist in each referenced table before a row can be inserted into a  referencing table.  You'll find an example as Families.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This simple little demo file illustrates two methods of forcing at least one family member to be entered per family.  Usually a form/subform would be used for this, and with this method in the demo the code in the parent form's module checks for a row in Families with no matching records in Family Members.  It does this in the parent form's Current event procedure, for when a user moves off a family record, and in the form's Unload event procedure for when a user tries to close the form.  If it finds a family with no members it informs the user and navigates back to that record.

With your hypothetical scenario, however, the number of rows per room/light fitting type would be predetermined, so another option, and probably a simpler one would be to automatically insert the rows into the table modelling the relationship type by building and executing an 'INSERT INTO' SQL statement in code when a row is inserted into the relevant referenced table.  The rows inserted would be determined by what type of fittings which are appropriate for the room, e.g. all rooms would require light fittings, but only bathrooms would require shower fittings.  Which rooms require what would be inherent in the model.  The user would then be presented with the set of questions as rows in a subform, in which they then would supply the answer to each by selecting from a combo box of possible answers.

2.  The table and column names are immaterial to the output, which is determined by the queries.  These can return whatever terms you wish as aliases for the table and/or column names.

3.  You only include the ResponseDateTime column in one table.  Which table you include it in depends on which entity type it is an attribute of, i.e. by which table's key is it functionally determined.  With Scott's simple model it is assumed that a respondent supplies all answers to a questionnaire on one occasion.  If you wish to track answers to each question temporally the date/time would be an attribute of the answers table.  For an example of a database for building simple questionnaires take a look at the file Questionnaire.zip in my SkyDrive folder at the link I gave above.  This file assumes a single date per response to a questionnaire, so the ResponseSessionDate column is in the ResponseSessions table.

Thanks to Ken and Scott's replies, I am seeing relationships within my application, and have an idea how to proceed with the design. However, I am still having a problem with some aspects, so perhaps I can pose some specific questions to solicit solutions.

1)   For example, I want my customers to give me their preferences for light fittings. I can create a "rooms" table and a "light fittings" table in a many-to-many relationship. This would give me what type of fittings would go in each room. My problem is that I want to force a response to every possible choice. So I would like the customer to declare a preference for all possible combinations (I see the solution to be a matrix, rather than relational). So for each room, and type of light fittings, I would get one of 3 or more responses (Yes, No, Depends on Price, etc) for each combination. So for the living room, the customer would not just list one preference, but says yes to ceiling light, no to wall lights, maybe to chanderlier depending upon the price, no to lamp stands. This gives me a complete picture of my customer preferences, and prevents a later "oh I hadn't thought of a lamp stand in the bedroom". Is there a way to do this in the relational database model?

2)  The purpose of my application is auto-generation of documents in Word, so Access will need pass unique fieldnames to Word. In a relational sense, I suppose the unique part is created by "tablename"+"fieldname". This could potentially produce some unwieldly merge fieldnames. Is this a problem, and should I take it into consideration when naming fields and tables?

3)  I want to keep track of changes to my customer responses, so as Scott suggests, a field with ResponseDate(+time?). How does that relate to all the other tables? Do I have to include the ResponseDate field in all tables (and update all tables at the same time). Intuitively this doesn't seem to be an efficient use of  the relational aspect, but no doubt I am missing a trick here.

I don't think separating a model into a set of databases, in modelling terms a set of different schemas or logical models, will help; it could introduce other problems. Insurance companies have come up against this when they try to integrate data from separate systems to bring all policies together after a takeover of one company by another.  I recall hearing of one case where this resulted in policies being related to the wrong policy holders.  But even if we rule something like that out as a possibility I don't see any advantages ensuing.

Continuing your decoration analogy, rather than having separate files per house you just have a Houses table which can be related to one or more clients, and to multiple room types, with the latter relationship type (modelled by a table, remember, so also an entity type) related to multiple paints. This is a simple model, but the same principles apply if the complexity of the model is extended.  In essence the process is pr4etty straightforward:

1.  Identify the entity types.

2  Identify the attributes of those entity which are determined solely by the whole of the key, e.g. city in my addresses example, where city is determined solely by the key (you know the address, you know the city), but not state which is not only determined by the key, but also by the non-key attribute city (you know the city, you know the state; note that we are talking about the city as an entity not the city name here, which can legitimately be duplicated).

3. Identify the relationship types between the entity types.  Some will be unary (1-way) and represented by a foreign key in a table, e.g. a State column in a cities table; others will be binary (2-way), ternary (3-way) etc and modelled by a table which resolves the many-to-many relationship type into two or more one-to-one relationship types, as with the OrderDetails example I gave earlier.

To establish the logical model use an entity relationship diagram, something invented by Peter Chen in 1976 and akin to a flow chart, with tables represented by boxes and relationship types by directional lines between the boxes.  Do this on paper before creating the file in Access.  Mentally test the model by asking as many questions as you can think of to see if it will cope reliably, e.g. can the model tell me how many litres of Oxford blue I will need for use in the kitchens in 4-bedroom houses in Stafford, England.  Only when you are satisfied that the model is robust should you create the file.

You'll have perhaps noticed my quote from Richard Feynman below my signature.  Whether he in fact said this I haven't a clue, but William Hurt did when playing him in the film The Challenger, and it is certainly in the spirit of his writings.  It is very apposite to many of the posts we receive where we give people a solution to a problem, which they try to implement, but without understanding how what we are telling them works.  If you are not familiar with the principles of the database relational model, be sure to do some background reading first.  You don't need to read Codd or Date from cover to cover; the Wiki article on the subject is not a bad starting point, and the concepts discussed should not present any difficulties for you with your scientific background.