AnsweredAssumed Answered

many-to-one relationships in one-to-many setting

Question asked by ultranix on Oct 28, 2012
Latest reply on Nov 9, 2012 by philmodjunk


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.