I am developing a database to track an industry and the interactions we have within it. I have several tables including a COMPANY table (stores company name, URL, sales etc.), an ADDRESS table (since a company can have more than one address). I am using ACCESS, and have a relationship set up between these tables and the ADDRESS data sits as a subform on the COMPANY form.

I would also like to add a table for CONTACTS, as well as a DIALOG table to track conversations with the contacts. Note that each CONTACT will need to be assigned to a particular address of the company for which they work -- in order to receive correspondence.

Thinking ahead to the future, each contact may leave to work for another company in the industry (or even a firm outside the scope of the database). The dialog we have with them will be important to recall, but if I link it only to the individual, I will loose the association when they leave the company. As such, its important to maintain a link at the CONTACT level as well as the COMPANY level (e.g. current employer or employer at the time of the conversation) since conversations may relate to company specifics, and not the departed employee . Also, it would be helpful to be able to maintain an employment trail contacts -- i.e. be able to see what firms they have worked at over the years.

This is a long question, and I have several thoughts on how to proceed, but I wanted to get some of your thoughts first since the time investment in building this is not insignificant.

Much appreciated.
David

I'd _strongly_ recommend that you not get hung up on the schema in the beginning. Just accept that your first design will be wrong, no matter how carefully you design it, but it is necessary to do the first design before you can do the second.

I'd suggest that you need to start by figuring out your processes. Person A is going to sit down at the computer, enter some stuff, run certain queries. Well, why? Because person B needs certain reports, etc. Why again? Because that's what some customer is expecting. You need to work out what everyone is trying to do and what they need to do it.

It helps to write it up on a whiteboard or even an easel and paper like they sell in Office Depot and draw up some plans of these processes. You can also do a storyboard of UI screens, basically draw each possible step of user interaction. Remember: you will have to place every single little widget, so if your storyboard is getting big it means your development time in Access will also be large.

Once you've got an idea of what you want you need to pare it down to what you can do, which may be less than what you need. Cut it all back to just a few screens. Then once you get a few screens working you can always go back and do more until you're happy.

With Access you'll need to do lots of little tests because it's often surprising what works and what doesn't work. It's a much better idea to create a new small database file and do the test there than to try to work out why a large complex database isn't working.

And last, once you've got questions that are more directly related to Access, you should know that there's a section of the board specifically devoted to that.

Agree with sco08y's statement about your initial design.
Disagree with his methodology.
If you start by thinking about processes, you will end up with a design that support those processes, and nothing else. New processes or unanticipated changes to processes will be difficult to support without major refactoring of the schema. This top-down, or "front-end to back-end" development is all too common. I see it all the time when our developers start with wire-frames and use-cases and work from there, creating tables specifically to support the front-end application. The resulting database design is invariably poor, inefficient, and inadequate to support future enhancements.
Instead, concentrate on modeling the business environment. The entities, their relationships, and their attributes. If you successfully model the business, then all business requests can be supported by your model.