Help designing a database

[Erick Solms]

I have to do a database for a real estate company. I am having problems, if
someone can help me I will really appreciate it. My email is
(e-mail address removed)
So far I have decided to create 4 tables. One for properties, one for
clients, one for realtors and one for the rentals. On clients the PK will be
the client's social, on realtors the realtors social will be the PK, and on
properties I dont know what to do. Each property only has one Identification
Number, but each property can have two or more clients related to it. So I do
not know how to link the property and rental tables with the client and
realtors table. Please any solutions?

 

[Vincent Johns]

No solution is in this message, but I have some suggestions.

I think you can combine your [realtors] Table with your [clients] Table.
A lot of the fields will be similar. You can use the Table Wizard to
help you; I'd start with the [Contacts] or [Customers] sample Table.
Choose the fields that you think may be relevant. There may be a couple
of fields that differ between clients and realtors, but you can leave
them empty when they don't apply.

Since I guess that some clients, and probably most realtors, have
interests in multiple properties, you should probably add a Table in
which each record links one human being (client or realtor) with one
property. Beyond having two fields, one for the key to the [Persons]
Table and one for the key to the [Properties] Table, a record in this
Table probably doesn't need to contain much. (Maybe a 3rd field -- its
own primary key -- but not necessarily.)

I also think that you should combine [Properties] and [Rentals], unless
you're renting tuxedos or airplanes. In the Table Wizard, you might
look at the [Assets] sample Table. These records would include, among
other stuff, a property description (perhaps including street address),
perhaps information about insurance, whatever you think you need to
know. If you maintain photographs, another field might list the file
number of the photograph.

You might want to generate your own identifying numbers (e.g., via an
Autonumber field) instead of storing a customer's social-security number
-- those could create legal troubles for you. Perhaps you're required
to keep them, but it's possible you don't have to maintain them inside
your database.

Next step, having set up a couple of Tables, is to try pulling
information out of them. What sorts of reports is your customer or boss
likely to need to see? Playing around with some Queries will give you
an idea of what fields you don't need to keep, and maybe you need some
information you overlooked. Make the needed changes and try again,
maybe using dummy data to give you a feel for what you can do.

Also, I urge you to fill in those "Description" fields in Table Design
View, to help make it quite obvious what kind of information each field
contains. I think you don't want any uncertainty there.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Jeff Boyce]

Erick

A word of caution ... leaving your email address in plain text in a
newsgroup is likely to get it "harvested".

It sounds like you have people (could be a client, could be realtor, but
could you ever have a realtor who was also a client?!), and you have
properties, and you have people-connected-to-properties.

That sounds like 3 tables to me. A Person table (using SSN as a unique ID
has some issues) would hold every unique individual. A Property table would
hold every unique property. The third table could have something like:

trelPersonProperty
PersonID
PropertyID
Role (client, realtor)

You could use the combination of PersonID and PropertyID as a primary key
(but this would preclude having a realtor be a client on the same property).
Or you could create an arbitrary ID (using Autonumber) for each
PersonProperty row.

You want to see all the realtor(s) on a certain property? Query the third
table by PropertyID and role. You want to see all the clients on that
property? Query the third table by PropertyID and role! You want to see
all properties a certain realtor is connected to? Query by PersonID and
role!

JOPO (Just one person's opinion)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/