I have a need to store 2 years of data for 4 different data points. This would be apartment rent data. There are roughly 200 data points (Average daily rent - every third day) over a 2 year period and there are 4 apartment types I need to store the data for, Studio, 1-bed, 2-bed, etc. I also have dozens of apartments in our database that I’d need this data for.
Is the best solution to have one record hold the 200 data points, maybe something like this, with 200 Data-x and Rent-x fields with the “x” representing data point 1 thru 200?
Property Name: Text
Style: Text
Date-x: Date
Rent-x: Number
This would leave me with 4 records per property, one for each apartment type, but each record would be really long.
Or, using the same structure but remove the Style field and have a Rent-x record that is Number/Multiple to store the 4 rents in one field. This would be only 1 record per property but still using a lot of Number/Multiple fields. (is there any advantage or disadvantage using Number/Multiple?)
Or what about using the same structure again but only without the “x” and have 800 records per property, one for each style and date combination? It’s a lot of records but they’d all be really short.
Property Name: Text
Style: Text
Date: Date
Rent: Number
I guess too that a table that is linked between my current “Property” table and a daily rent table might be an alternate solution but I don’t have a feel for how that might work or if it’s just as much work on the processing as any of these options.
I appreciate the response however I’m not tracking individual apartment information, nor tenants. I have a Property table that houses the data you mention for properties (among other items) but not tenant or transaction data.
I’m trying to determine the optimal solution for a table related to the Property table to house average rent data for 4 apartment types/styles within each of those apartments. The result being the Average Daily Rent for all Studio units within an property, Average Daily Rent for all 1-Bed units, etc.
In the chart that I’ll be creating, similar to that shown below which only shows 1-Bed, 2-Bed and 3-Bed units, each point along the line is the average of all the unit rents for that style for that date. This results in 200 data points for each of the 4 apartment styles that I need to store for each property. In Excel this is simple, 5 rows (Date, Studio, 1-Bed, etc.) and 200 columns of average rental data with each property on its own tab/worksheet. It’s the structure of this table that I’m interested in defining in the most efficient way, and I’m not advocating for either of the ways I mentioned above…but I would like an opinion on what might be the best in terms of speed to the display of this data to the user.
Was there a suggestion that you’d offer up? I’m not committed to a solution yet and was asking for some advice but your previous reply addressed a need that I didn’t have. I’d be interested in what format you would suggest for the data I’m trying to store and retrieve.
I would agree with kevnobrega. Store it in the logical way - Property/Tenant/Transaction and then do what you like with the data. As long as they are related correctly you can manipulate as you wish.
Seriously…I’m trying hard not to be disrespectful but I guess I’m not being clear.
I DO NOT have tenants or transactions. I never have mentioned these things other than to say I don’t have them. I have ONLY total average rent for 4 types of units. In other words, if a property has 100 1-Bed units, I take the average rent of ALL 100 units to come up with a figure. I have this same data for 4 unit types (Studio, 1-Bed, 2-Bed, 3-Bed), and I have the average of these values every 3 days. This creates nearly 200 data points for each unit type for a 2 year period.
My question is which would be the best table structure to store this data. Is it more efficient to have 200 records for each property, with one record for each period and containing the fields Property Name, Date and a list field of the 4 Average Rent values, or maybe 800 records for each property with each record containing Property Name, Date, Style and Average Rent for one unit type, or maybe 4 records per property with Property Name, Style and 200 Average Rent fields per record?
Or…something else entirely that I haven’t considered…but please, giving me a list of tables to create doesn’t answer the question…not at all.
Are all your units identical and in the same areas? If not store each unit individual as it has its own characteristics. Will all two beds remain the same with the same characteristics? I sort of understand your predicament as I am building a similar project. Just my two cents, hope it helps in some way.
