Database theory! table relationship question

[marconey]

Hi everyone!

I am currently trying to develop a database for my real estate business. I have been a Macintosh fan for over 8 years. I have also come to like the elegance of FM Pro. Now with the introduction of the relationship graph, I thought I can finally put my relational database design theory courses to practice!!

Here's the scenario: I want to keep track of my clients (both purchasers and sellers - I am treating them as one entity), their transactions (again I am treating home purchase transaction and home listing transaction as one entity), various contingencies and deadlines (both same things) that I have to keep track of, various vendors that my clients might need, various realtors that might be representing my clients or that I might be dealing with as part of the transaction, the property and a list of things to do. The above entities are pretty straightforward. Now comes the part where I am stumped, I also need to ask questions during my first contact with, say a prospective seller, such as how long have you owned your home? How soon would you like to sell your home? and Are you currently working with another realtor? etc. Now which table would I put these questions into? Should I create a table called questions and put all the different questions there and then create a many to many relationship from Client to Questions (and create a third join table in the middle as part of the process)? I am afraid of disturbing the carfully formulated art of "normalization" by putting the info. in the wrong place(!). To me these questions don't seem like they are attributes of any of the entities that I have mentioned before... I have tried to make the senario as descriptive as possible since you might not be a realtor (I just got started and feel like fish out of water - but I am caching up!!). Would really, really appreciate the help in advance!!

Thanks,

Kenneth.

[Ender]

Now comes the part where I am stumped, I also need to ask questions during my first contact with, say a prospective seller, such as how long have you owned your home? How soon would you like to sell your home? and Are you currently working with another realtor? etc. Now which table would I put these questions into?

If they will be asked only once for a client, I'd put them in the Client table. If they will be asked once for each transaction, I'd put them in the Transaction table.

[marconey]

Great! but if I feel that I might think of more questions to ask them later (after I have designed the database), then I think it would be wise to have a "ClientQuestion" table? Because then I'd be able to add more questions to that table without distrubing the rest of the tables and simply relate records in that table to Client or Transaction tables. Dos this make sense?

Thanks,

Kenneth.

[Ender]

Sure, a Question table is a good idea if you expect the questions to change over time (Actually, a Question table and Answer table.) This structure is most useful when the questions have a similar format, and you want to do reporting on them later.