Structure: Actions Journal w/ many details tables
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:
Amount (of offer/listing/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?