AnsweredAssumed Answered

Structure: Actions Journal w/ many details tables

Question asked by SkipperID on Aug 15, 2012
Latest reply on Aug 15, 2012 by philmodjunk

Title

Structure: Actions Journal w/ many details tables

Post

Trying to figure out the best structure for a property management db.

I have Assets (Parcels, Buildings and Units) that I want to create a journal of activities/actions for.

Each category of ACTIONS has enough specific field information that I have divided them into their own tables: Real Estate, Maintenance, Correspondence, Notes.

For Example, the fields in Real Estate are:

Type (Listing/Inquiry/Offer/Counteroffer/Sale/Purchase)
Date
ExpireDate
Buyer
Seller
Amount (of offer/listing/etc.)
Realtor
...etc

In order to connect those activities with their details, I am considering an ACTIVITY table which connects the ASSET to ACTION DETAILS

So the ACTIONS Table is: ACTIONID, ASSETID, ActionCategory, ActionDetailsID, CreatedDate

*** Which table those actiondetails are in is dependant upon the ActionCategory.

 

Here's how I will use the data:

1. Imagine now that I inputing a transaction.

A. I create a new record in the Actions table.

B. based on the Category of the action, I know the which action details table to reference (which will open on the form for input) 

 

2. Imagine now I am creating a report of all ACTIONS for the past week.

A. I search the ACTIONS table by a date range.

B. From those records i somehow pull the details from their Details using ActionCategory and ActionDetailsID

C. Sort/Group by Date/Category

 

- I'm trying to model this on an order/orderdetails set up, but the trick seems to be that there are many different orderdetails tables because the products have such different specs.

Is there a better way to approach this?

Thanks!

 

Outcomes