CODEWITHSUNDEEP
mysqldatabase
NateK
NateK

Reputation: 3

Database Design - Entity–attribute–value Model Query

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.

Upvotes: 0

Views: 523

Answers (1)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

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?

Upvotes: 1

Related Questions