Suggestions for database design (long)

[sucoyant]

Good day.

I need some suggestions as to how I should lay out my tables and relationships in a database I'm going to create.

Background: Every day an employee will open up a log of customers who are missing items. The first thing they will do is check to see if 2 weeks have passed since they last mailed the same documents to the customers. Every 2 weeks, the same documents are sent to the customer and then the date which is 2 weeks old, is then replaced with the current date, so they have something to reference.

With every mailing the following things are sent to the customer: Notice of Missing Items (Which lists all items that are needed), and the actual missing document that the customer is supposed to fill out and return

Now, each one of these items has has a legal document that should go along with it. For example; If a customer is missing his Odometer Statement, the report/document that goes with it is called "Odometer Disclosure Statement".

Business Rules: One customer can have many missing documents(aka Items).

Tables: I can think of 3 tables off the bat. Customer, Forms, and MailLog. Does this seem right?

Relationships: One Customer to many MailLog. Many MailLog to one form.
I think you have to do it this way because sice there are many customers to many forms, you have to put a table in there to break up the many to many relationship.


Outcome: What I would like to accomplish is a database that will, when opened every day, check to see if any dates in the "Date Last Mailed" field are 2 weeks old. It will then check to see what they are missing, and print these items which are saved as reports, along with the "Notice of Missing Items". It will then replace the "Date Lase Mailed" with todays date.

Any suggestions as to how I would lay out the tables and relationships? I would very much appreciate it.



________________________________________

Buddha. Dharma. Sangha.

 

[BNPMike]

You probably need Customer (obviously) then Item which has the types of thing people can get missing. If there is one form per item you can store the information in Item.

You then need Customer-Item-Date. This is the incident. You need date because customers may order the same thing more than once and have more than one outstanding incident. Providing you only want the current view, you can store mail dates etc in this table.

Possibly forms aply to a class or several classes of items so you may want something that classifies items and thus separates forms into another entity.