Table Structure Idea help

[TechMike]

I need help with table structure, They keep way too much info. Now that I have said that here is what I need help with. I have been asked to make a small "Ha ha" Database this is what small became. Most of the data for this is in a spreadsheet now I know I can import it where I want it. The main concerbn is that the main piece of information would be the Job Address. Because of all this other information I was not really sure how to setup the tables here is what I have started .

Buyers Information (tbl)
Fname
Lname
Address City State Zip (with a look to qry)"cool zipcode code"
Phones (3)
Email
Ect.

Buyers Realtor Info (tbl)
Fname
Lname
Company Name
Address City State Zip (with a look to qry)"cool zipcode code"
Phones (3)
Email
Ect.

Sellers Information (tbl)
Fname
Lname
Address City State Zip (with a look to qry)"cool zipcode code"
Phones (3)
Email
Ect.

Buyers Realtor Info (tbl)
Fname
Lname
Company Name
Address City State Zip (with a look to qry)"cool zipcode code"
Phones (3)
Email
Ect.

Job Information (tbl)
Address
City (Filled in by Zip)
State (Filled in by Zip)
County (Filled in by Zip)
Zip (with a look to qry)"cool zipcode code"
RoofType (With a lookup to A list tbl)
RoofCondition (With a lookup to A list tbl)
Job Notes (memo)

Appointment Information
Time (now real sure how these time and date will work)
Date ( " )
Appointment Type (With a lookup to A list tbl)
Contact Name
Salesman (With a lookup to Salesmen Table)
Appointment Results (memo)
Referral

As I mentioned this data is in a spreadsheet and most every row has all of the above information ("ha ha, small") but I wanted to get some other Ideas and see if anybody had a better one before I tryed to make it all work.

Thanks

 

[Pat Hartman]

Notice the commonality of the fields in the first four tables. CompanyName only occurs twice but all the others occur in every table. That should clue you in to the fact that you should have only a single table rather than four tables.

Given the names of the first four tables, I don't understand what the Job table is. It sounds like you do roof work but what does that have to do with buyers and sellers and real estate agents? If your work is actually roof inspection, you need the address of the property being inspected to be in the Job table. If you are assuming the Job address would be the seller's address, that would be a problem if the seller was selling a property that was not his primary residence.

Now that you have combined the first four tables into one, you need a way of identifying each persion's role in the transaction so you need a relation (or junction) table. This table includes the JobID, the PersonID, and the RoleID. RoleID will come from a lookup table that defines the roles of the people in a transaction.

The Appointment table seems to identify yet two more people - a contact and a sales person. Those also belong in the people table. The Appointment table should, I think, include a reference to the Job table so you know what the appointment is for. You probably also need a junction table between Appointments and people so you know who is meeting. In this case I think the appointment table may also need an address because you will need to know where the meeting is going to take place.

 

[TechMike]

Pat,

Here is what i was "was" thinking or what i was tring to do.

The sellers address and the buyers address will most likely be the same as the job address but sometimes it is it will be just the owner, so the job address is always there but it may differ from the buyers or the sellers, as in the case of investment prop then it is going to be the realtor address. I was tring to make it smaller than what It was starting to turn into. I am going to setup the tables in the morning and follow your advice once i get them built and I can see more in detail what mean. Thanks for your quick response. I have read many post/replies or yours your advice always seem to lead us stray sheep back to the flock.

Mike

 

[Pat Hartman]

Thanks and Welcome aboard.

If there will be a lot of duplication in the address data, create a table that holds ONLY the address information. Then create a 1-many relationship between the people table and the address table. This relationship is harder to manage than having the address embedded in each person record so you need to be careful when an address change is requested to find out if the change is for all uses of the address or just the person currently showing on the form. An example of this from another application would be a church membership db with separate records for family members but all sharing the same address record. If a child moves to a new address, you don't want to change the family address.

 

[TechMike]

Pat,

It has been a bsy day I made it this far let me know if I am going the right direction

 

[Pat Hartman]

Sorry Mike I don't have time to look at it today.

 

[TechMike]

Pat ,

I am in the same boat as you way much work thanks anyway but if you do get the time just let me know ..

Thanks again
Mike