How do I create create table of multidimentional data?

Question

0.00/5 (No votes)

See more:

i have one master table which is about 100,000 records in it.

table structure is:
------ --------- --------
size | machine | Speed |
------ --------- --------
1 M1 100
2 M2 50
3 M3 20
4 M4 60
5 M5 40
.... ... .....
1000 M100 50

Now,
Each Size on Every Machine has different speed
Each Machine runs on diffrent speed on perticular size.

If i continue to above approch then records will be 1000*100=100000
which is not tollerated by database engine.

how can i design this table?

What I have tried:

I do Something like this
Size M01 M02 M03 ----- M100
1 100 50 20 30 // speed value

to

1000 100 30 20 ------ 20

but problem occurs when i try to query by passing column name by parameter

Posted 17-Sep-17 4:19am

Hardevsinh Mori

Updated 17-Sep-17 8:40am

Add a Solution

Comments

A_Griffin 17-Sep-17 10:33am

I am not 100% clear:
You have 100 machines, which run at different speeds according to different sizes (of something) – is that right?
First off: are you sure you even need to store these in a table? IS there not a formula that can give you the results you want?
But, assuming you do, I think you have little choice – though I’d have thought the number of records should be the number of machines times the number of sizes which = 5000, which isn’t so bad.

2 solutions

Solution 2

Quote:
If i continue to above approch then records will be 1000*100=100000
which is not tollerated by database engine.

An SQL server have no problem handling a table with millions of records even in smallest hardware if design is correct.
Your example show unique value, you don't explain the purpose of the table, nor the usage.

Quote:
how can i design this table?

What advice do you expect with this nothing ?

Quote:
but problem occurs when i try to query by passing column name by parameter

That is the reason why everyone will tell you it is a bad idea, whatever is the usage of the table.

You need to refine the question.

Posted 17-Sep-17 8:40am

Patrice T

Comments

Wendelius 17-Sep-17 23:37pm

Countered the downvote.

Patrice T 18-Sep-17 0:20am

thank you.

[no name] 18-Sep-17 2:20am

What advice do you expect with this nothing ?
^^
Be Kind To Learner,Don't loose your passions while Answering

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2017-09-17T06:46:00

Solution 1

If I understand your question and suggestion (in what I have tried) correctly, do not put machine name as a column. Machines are values so they should be stored as rows.

Think what happens if a new machine is needed... You would need to change the database structure and it most likely would also affect the calling program.

What comes to the original structure, if the size and machine define the speed then your current table structure seems valid.

You wrote:

If i continue to above approch then records will be 1000*100=100000
which is not tollerated by database engine.

A table 100'000 rows is not a big table. Any modern database engine should be able to handle such amount of rows without any problems.

So if the database really has problems with such small amount of rows I would consider changing the database. Otherwise have a try with it.

Posted 17-Sep-17 6:46am

Wendelius

Comments

[no name] 17-Sep-17 23:13pm

What i have tried section table works perfectly but i am looking at future problem that's why i had posted this question.

[no name] 17-Sep-17 23:15pm

In some cases this reocords may become large.

Think what happens if a new machine is needed... You would need to change the database structure and it most likely would also affect the calling program.

^^^
For above purpose i had posted question.

Wendelius 17-Sep-17 23:36pm

Just to make sure we're on the same page: This is the key problem here. In database design you should never use values as column names since the number of values changes. Doing so is against all design principles.

And as said it's not just that it's against the design principles but this approach will lead to severe problems when things change. Also using this kind of approach would require you to write unnecessary and unnecessarily complex code compared to the task.

I think I saw in some question that you're using MySQL. if that is correct, MySQL should handle hundreds of millions of rows easily so the amount of rows you're having is nowhere near the limits of the database engine. So having hundred thousand rows does not justify the approach you proposed :)

[no name] 18-Sep-17 0:30am

Yes, I am using MYSQL With InnoDB Engine and i am not sure about that engine can handle or not because every single entry needs this table values. so i was little worried about. But now i am clear about my approch.

[no name] 17-Sep-17 23:16pm

Thanke You Sir, For Your Worthy Explanation.