How to build a "detective" database? (1 Viewer)

selvsagt

Registered User.
Local time
Today, 15:53
Joined
Jun 29, 2006
Messages
99
I am working on a database to find "common ground" between persons of interterst, real estate and company ownership. This is of an investigative nature.

I have tried to google a solution, but the words relation and database gives trillions of hits, but no hits (i can find) with the kind of relationships I am looking for. Google itself has been my primary resource for finding all this information, but I need to structure it.

I am not sure on how to design the table structure.

For example.
Lets say we have company A.
It has the owner John Dow, and chairman Jane Dow.
They owned realEstate1 and realEstate5.

Company B has owner Roy Brown and Jane Dow, and chairman Black, and ow
Real Estate C. And suddenly a year later I find that Real Estate5 now is owned by company A.

And the list goes on and on and on...and the real estates change hands, the companies change hands...

I have considered having a table called "observation", and the input would be company C, real estate 4, owner A, emploeey C and so on.

I would then like to have a way to type in a real estate, then get ALL related information about it (all observations). In the same way I would like to type in an Owner or Emploey and get the same results, or just type in the company name in with it get all information in the database that has a connection.

I obciousely need a table for the observation, then one for the company, a table for employe, one for real estates and so on. But how would I set up the relationships between these tables? This would be a many to many to very many relationship structure...

Does anyone know of a sample that does something like this?
 

spikepl

Eledittingent Beliped
Local time
Today, 15:53
Joined
Nov 3, 2010
Messages
6,144
It's a bunch of many to many.

Fx:

tblCompanies
----------------
CompanyID
CompanyName


tblRealEstate
--------------
RealEstateID
RealEstateName


and the ownership can be stored like this

tblCompanyRealEstate
-------------------------
CompanyRealEstateID
CompanyID
RealEstateId
FromDate
ToDate


so one company can own one or more real estates each in some period of time, and one real estate can be (co)owned by one or more companies

the remainder of your relations seems similar, e.g., ownership of a company by one or more indioviduals
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:53
Joined
Jul 9, 2003
Messages
16,244
Without having any idea what I'm talking about! My assumption is the essence of the problem is the property and the person.

The property cannot change and the person cannot change (although the person can change their name) the property can move from company to company, person to person. Persons can move from one company to another.

So the thing to track would be a property (real estate) person to person. Match the person to the real estate with a many to many table, the rest is just superfluous.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:53
Joined
Feb 19, 2013
Messages
16,553
try googling 'entity relationships' or similar.

CRM systems are based around this concept which is what it sounds like you are trying to build
 

selvsagt

Registered User.
Local time
Today, 15:53
Joined
Jun 29, 2006
Messages
99
Hi.
Thanks to you all for the advise.
I have tried some different solutions, and found that "weak" relationships (just a bunch of relationships between the tables) actually works.
The purpose of this database is basically to give me fast way of finding connections between people, property and companies.
I have one table for each, and a table called tblObservation. The observation is the table for the many-to-many relationships. I have a bunch of subforms each filtered by the options I choose in the frmObservation. This gives multiple lines of the same info, because the relationship is weak, but its good enough for my purpose.
I think i mark this as solved for now :)
 

Users who are viewing this thread

Top Bottom