Need an advice for 3D Table matris

I have 1000+ device and each device recording daily 24 or 3 value how do you advice me to store all this value on mysql
3XLcomAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NerdsOfTechConnect With a Mentor Technology ScientistCommented:
you can then query the table for data easily as well:


SELECT number from Machine_Table
WHERE machineID = 1
AND timestamp BETWEEN #2010-09-23 02:00# AND #2010-09-23 10:00#


results:
7,2589237
2,3859480

0
 
rockiroadsCommented:
so one device may store either 24 values or 3 values? is that what you are saying?

what are these values? are they fixed data or random?

eg   a one to many

Table DEVICE
DeviceID
DeviceName

Table RECORDING
RecordingID
RecordedValue


if recordedvalue is fixed then many to many = one to many to one

Table DEVICE
DeviceID
DeviceName

Table RECORD_VALUES
RecordID
RecordValue

Table RECORDING
DeviceID
RecordID
and anything else associated to a device/recording value

0
 
3XLcomAuthor Commented:
in reality some devices send 3 value in one day some of them sends 24 but if i make one table it nearly makes 720.000 line on one table so it makes a huge problem in a few months
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
rockiroadsCommented:
so what values are you recording? if its just numbers then it should be fine. One to many is flexible, will allow other variations other than 3 or 24.

Mysql is scalable so cant see the issue
0
 
3XLcomAuthor Commented:
numbers like 1,5343434 0,3423423 sth. like this
0
 
NerdsOfTechTechnology ScientistCommented:
you said the old way would create 720.000 slots so I assume if we divide by 24 you are dealing with roughly 30.000 devices (30 thousand)

if the devices have an id you can actually create a 2d relationship:

machineID| number|timestamp
1| 2,3859480|2010-09-23 07:12:10
1| 7,2589237|2010-09-23 04:12:10
1| 3,4523522|2010-09-23 17:12:10

2|#|date 01:00
2|#|date 02:00
2|#|date 03:00
2|#|date 04:00
2|#|date 05:00
2|#|date 06:00
2|#|date 07:00
2|#|date 08:00
2|#|date 09:00
etc


Then you are recording JUST the data required without issues and have the scalability for man, many, more devices in the future.

0
 
3XLcomAuthor Commented:
my time stamp values a little bit different then how mysql understand sth like :
2010-08-10T00:00Z
0
 
rockiroadsCommented:
I cant see how you are going to get around this

the one to many approach as I described earlier is probably the best

Table DEVICE
DeviceID
DeviceName

Table RECORDING
RecordingID
RecordedValue
DeviceID

As long as you set your indexes correctly (primary on DEVICE.DeviceID and RECORDING.RecordingID, secondary on RECORDING.DeviceID and duplicate index on DeviceName)
then you should be fine in terms of searching records

You are just storing time stamps against a particular device. You will not recreate device names so no duplication involved there. Storing numbers instead of text is better (as the id's would be autoincrementing anyways)


How long do you want to keep the data for? maybe introduce some sort of archiving process.
0
 
mattibuttCommented:
hi NerdsOfTech

i need to talk to you about some dilemma i have how do i contact you buddy?
0
 
NerdsOfTechTechnology ScientistCommented:
Sure, what did you need.
0
 
3XLcomAuthor Commented:
I already use this way but i think this is the only way
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.