new database. need help with tables and relationships please?

Here is a picture of my current relationships:

http://img140.imageshack.us/img140/7...ationships.jpg

Hi all,

I have just joined the community and I have very basic Access skills. I am
using Microsoft Access 2007 and I am trying to create the following database.

I have attached a picture of my relationships. Please ignore all the
relationships that i have made as i know they are wrong. Ill try summarise
what kind of database i am trying to make:

There are two ‘sides’ to the database. The one side is the ‘Stock’ side and
the other side is the ‘Person’ side. I then used an affiliation table to link
the two. If we start with Stock.

Stock is basically a ‘leasable’ or ‘saleable’ unit. There are 3 types of
stock (Retail, Office and Industrial). Each stock has a status which is
basically ForSale, ToLet and OffMarket. A stock could be for Sale AND To Let.
What I did was create a Lookup under Status which is fine and I allowed
multiple values as a stock can be ForSale AND To Let.

However, for Stock Types, each type brings new properties. If you see under
the stock table I have listed all the properties that are common to ALL stock.
Then where the properties of Retail, Office, and Industrial differ I have
created new tables for each type. My problem here is that a stock could be
Both Retail and Office, or Office and Retail, etc. Ideally what I would use
is the same sort of option like with ‘Status’ as mentioned before, but if I
choose for example 'Office' a pop-up for the properties of an office space
should appear, and if I choose 'Industrial', the properties for Industrial
should pop-up, or I can choose both ‘Office’ and ‘Industrial’ and the
properties for both should pop-up. I hope Im not confusing. The reason why I
want it like this is because if I need to search through all the stock for
Offices, then all the ‘Stock’ that has offices part of it should be the
result.

That is just the Stock side.

With the ‘Person’ table, I have done something similar, where a ‘Person’
could be a Client, Property Manager, Landlord, Tenant or Other. However, a
‘Person’ could be many of these types at the same time. Eg. A ‘Person’ could
be a ‘Landlord’ for 1 ‘Stock’ and also a ‘Tenant’ for that stock. A ‘Person’
could be a ‘Tenant’ in 1 stock and then also a ‘Client’, but a ‘Client’ has
no relation to stock at all. Anybody could be a client, as a client is a
short term profile for a person. If a ‘Tenant’s’ lease is due to expire in 2
months and they wish to look for new premises, then he becomes a ‘Client’ but
is also a tenant at the same time. So you can see where a ‘Person’ would have
more than 1 type. And then depending on what the ‘Client’ wants (Retail,
Industrial, Office).

Then the whole crux of this is that I should be able to input a new stock
with or without an affiliated person, or a new person with or without an
affiliated stock.

Man I have been struggling a lot with this. Got any ideas? I am starting to
think that I have all my tables wrong.

I think it would be better to explain my use for such a database. Once it is
complete I would like to use it as a tool to:

a) record all 'stock' in my area and search within the parameters of 'Area',
'Size', 'GrossRental', 'Status', etc.
b) record all 'person'(s) by their type and affiliate them to a stock
(depending on their type.
c) be able to cross reference 'client' requirements with 'stock' and the
stock type. eg. if a client is looking for industrial space, should be able
to cross reference his requirements with all the industrial stock

Hello Tonypony

I noticed that nobody answered. You have a lot going on there. I think
that you would never several substantial post exchanges for respondent to
know engough to give an definitive answer, and then lots of posts to tell you
how to do everything that you want to do.

That said, here's my wild guess based on insufficint knowledge.

Your current structure is OK, although not fully normalized. The lack of
full normalization will make some of your searches more llaborious. Full
normalizaiton would also make your structure more abstract.

Your structure is also complex to cover all of the possibilities. Long
term you might make a practical review to see which of all of those
possibilities need to be recorded, with possible simplification iin mind.