0

I have finally stumbled across a problem that I can't already find the answer to on SO...

I am working on a database that will store recorded sampled data recorded over time. Originally, the client had built a table that was very specific to the data that they were currently recording, however, they would are concerned that as they expand, the collected data may begin to vary, and more, or at least different, columns may be required.

Current model:

+------------------+     +------------------+
|    FACILITIES    |     |   DATA_RECORD    |  
+------------------+     +------------------+  
| ID               |     | ID               |
| NAME             |     | FACILITY_ID      |
| DESC             |     | TIMESTAMP        |
| etc.             |     | TEMP_WATER       |
+------------------+     | TEMP_AIR         |
                         | pH_WATER         |
                         | etc...           |
                         +------------------+

I think the database should be designed as follows:

+------------------+     +------------------+     +------------------+
|    FACILITIES    |     |   DATA_RECORD    |     |   COLUMNS        |  
+------------------+     +------------------+     +------------------+  
| ID               |     | ID               |     | ID               |
| NAME             |     | FACILITY_ID      |     | NAME             |
| DESC             |     | details etc..    |     | DESC             |
| etc.             |     +------------------+     | UNITS, etc...    |
+------------------+                              +------------------+

+------------------+ 
|    DATA_POINT    | 
+------------------+ 
| ID               | 
| DATA_RECORD_ID   | 
| COLUMN_ID        | 
| VALUE            | 
+------------------+

My questions are:

  1. Is this the best way to design the database, or is there a better way that I am not familiar with.
  2. How do I form the query statement to return each data record with each of its associated columns?
  3. Is there a generally accepted good data-type to use as DATA_POINT.VALUE? e.g. float, decimal...?

Thank you so much for your help.

Sincerely,

Nate K.

NateK
  • 3
  • 1

1 Answers1

1

Looking at your requirements, I think you'd be leaning more towards the Entity-attribute-value type of design, which in general is tough to query (although it is fairly simple to set up) and not very scalable.

You can search for EAV models on this site or on google to see discussions about this.

http://www.simple-talk.com/content/print.aspx?article=843

Entity Attribute Value Database vs. strict Relational Model Ecommerce

http://tkyte.blogspot.com/2009/01/this-should-be-fun-to-watch.html

For your case, can you post the different types of Facilities and datapoints that you'd need to collect? May be you can use types and subtypes to model this?

Community
  • 1
  • 1
Rajesh Chamarthi
  • 18,568
  • 4
  • 40
  • 67
  • Thank you! The second link helped a lot. There is a fairly detailed front end that goes along with this database, and so I think option 3 (from the second link) will be the best solution - _Option 3, Combination (model entities "properly", but add "extensions" for custom attributes for some/all entities)_. – NateK Jul 14 '12 at 02:03
  • I thought so too. Good luck :) – Rajesh Chamarthi Jul 14 '12 at 02:19