DB relationships

Hello,

I'm building a db for an estate agents. It has five tables. If you imagine a
circle, with table buy_sell at it's centre, and four other tables named:
1/ client [containing all client data - sellers]
2/ buyer [containing all buyers information = mirror image of client]
3/ property [containing all the property for sale data]
4/ enquiries [bring together buyers and property they have enquired about]
around it's circumference.

The relationships as I see them are: seller to properties = one seller can
have many properties thus many:1?

buyer to enquires = many buyers can enquire about one property thus many:1?

property to enquires = only one property should enter here with buyers
enquiries, thus 1:1?

property to buy_sell = one property to one buyer to one seller thus 1:1?

seller to buy_sell = one seller to one property here thus 1:1?

buyer to buy_sell = only one buyer here to one property thus 1:1?

Not sure if I should ask this question here, but if someone could look and
either confirm or [not] as the case maybe, if this is correct. I've just
checked and have actually made all relationships 1:1 and think this maybe
incorrect....

Thank You




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: DB relationships

=20
Hi David,

I would like to propose a different logical design to your database.

It seems to me that you have 3 mayor objects to be described by the
database:

1. People
2. Properties
3. Inquiries - that people inquiring about properties.

Also, it seems to me that a person could be a buyer and a seller the
same time.

So, I am proposing the following table layout:

1. People - description of people. Only description of a person or
people - Last, First name, phone, .... You might need think about how to
includes someone's family in the database

2. Property - description of a house and house related things.

3. People roles - just 2 (two) entries - buyer, seller

4. Current property - who lives where. For example - ID 1( primary key)
from table PEOPLE lives in ID1 (primary key) - table PROPERTY

5. Inquiries - who inquire about what and in what roles - ID1 from
PEOPLE made an inquiry about ID22 from PROPERTY as buyer from - PEOPLE'S
ROLES

In this scheme you are going to have the following relationships -=20

PEOPLE many-to-many INQUIRY
PROPERTY many-to-many INQUIRY
PEOPLE many-to-many PEOPLE ROLE

This is a typical solution in many-to-many relationships database.

Hoping this helps


Mikhail Berman

-----Original Message-----
From: grovesdavid@linkhomes2000.co.uk
[mailto:grovesdavid@linkhomes2000.co.uk]=20
Sent: Wednesday, October 12, 2005 6:13 AM
To: win32@lists.mysql.com
Subject: DB relationships

Hello,

I'm building a db for an estate agents. It has five tables. If you
imagine a circle, with table buy_sell at it's centre, and four other
tables named:
1/ client [containing all client data - sellers] 2/ buyer [containing
all buyers information =3D mirror image of client] 3/ property =
[containing
all the property for sale data] 4/ enquiries [bring together buyers and
property they have enquired about] around it's circumference.

The relationships as I see them are: seller to properties =3D one seller
can have many properties thus many:1?

buyer to enquires =3D many buyers can enquire about one property thus
many:1?

property to enquires =3D only one property should enter here with buyers
enquiries, thus 1:1?

property to buy_sell =3D one property to one buyer to one seller thus =
1:1?

seller to buy_sell =3D one seller to one property here thus 1:1?

buyer to buy_sell =3D only one buyer here to one property thus 1:1?

Not sure if I should ask this question here, but if someone could look
and either confirm or [not] as the case maybe, if this is correct. I've
just checked and have actually made all relationships 1:1 and think this
maybe incorrect....

Thank You




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=3Dmberman@ivesinc.com


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org