many-to-one relationships in one-to-many setting
Here I am trying to set up database for my real estate activity. I use 3 tables and am in need of 4th. 3 I use are:
1) Base (table of contacts, it would basically contain personal information: name, phone, email)
2) Query (table of queries, as 1 contact my have many queries, because at the same time the same person can be either seller or buyer and can be a buyer of several flats/houses)
3) Action (table of activities of the particular query: calls, views, emails, etc.)
As for now I have those 3 tables related in the way listed below:
BASE::id_conta = QUERY::id_conta (as one contact may have many queries)
QUERY::id_query = ACTION::id_query (as one query may have many actions)
What I want to accomplish is simply two things:
1) Track how many views are for the particular object (flat/house). To achieve this i'm contemplating to add another table: OBJECTS. Therefore, I would move object_type (flat/house) to OBJECTS table and leave only id_query, id_conta, and id_object and action_type (buyer/seller/tenant) in QUERY table. I just don't know how would i squeeze in that OBJECTS table, because 1 query can only contain 1 object.
Plus, in which table should i put that calculation of views field?
2) Track how many buyers are contacted regarding particular object (flat/house). That is, I set a record in a queries table action_type = Buyer, add address (or object_id, if I'm able to solve the problem in 1st question, above) and count how many buyers are contacted regarding that particular object.
This would be very helpful to automate reports that I send.