Solved

Need an advice for 3D Table matris

Posted on 2010-09-22
11
487 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:3XLcom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33740901
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
 

Author Comment

by:3XLcom
ID: 33741002
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33741038
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:3XLcom
ID: 33741064
numbers like 1,5343434 0,3423423 sth. like this
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33744465
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
 
LVL 19

Accepted Solution

by:
NerdsOfTech earned 500 total points
ID: 33744508
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
 

Author Comment

by:3XLcom
ID: 33745260
my time stamp values a little bit different then how mysql understand sth like :
2010-08-10T00:00Z
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33745722
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
 
LVL 11

Expert Comment

by:mattibutt
ID: 33761579
hi NerdsOfTech

i need to talk to you about some dilemma i have how do i contact you buddy?
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33779981
Sure, what did you need.
0
 

Author Closing Comment

by:3XLcom
ID: 33873743
I already use this way but i think this is the only way
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MYSQL responding very slow 3 49
MySQL limit and not so limited 13 36
SQL - Simple Pivot query 8 27
MS SQL Conditional WHERE clause 3 37
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question