Best way of storing array on database

Question

I have a C# application that requires a set of arrays such that,

double[10] x = {0, 1.5, ......}
double[10] y = {0, 2.4, ......}

I need to store these data on Sql-Server and until now I create a table which has columns like x1, x2,...x10 and y1, y2,...y10. So for my first set I have a table row such that,

no  x1 x2...x10   y1 y2...y10
1   0  1.5 ....   0  2.4 ....

So when I need data on database, I use a code like (with entity framework),

x[0] = data.x1;
x[1] = data.x2;
.
.
x[9] = data.x10;
y[0] = data.y1;
y[1] = data.y2;
.
.
y[9] = data.y10;

But this way does not look right to me. Is there a simpler way of doing this (like x = data.x). Should I change the way of keeping data on sql ?

Thanks for reading and for your help.

This looks to me like a job for BLOB? Just persist the whole object in serialized form. — Fildor
– Fildor, Commented Feb 23, 2018 at 8:57
What do you want to do with that data? Different scenarios need completely different designs. Do you want to query by individual values, filter, retrieve individual entries? Then it should be a separate table. Is the number of entries fixed? Perhaps you could use sparse columns. Do you only want to load/save the array as a whole? Convert it to JSON and store it in a string field. You could even parse individual values using SQL Server's JSON support. Are the values integers? You could use a delimited string instead of JSON, saving a few bytes. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 23, 2018 at 10:07
Thanks for the answers. I think JSON is the trick I was looking for. — xmp222
– xmp222, Commented Feb 25, 2018 at 18:26

Harald Coppoolse · Accepted Answer · 2018-02-23 14:09:42Z

You should ask yourself, if you describe the problem that you try to solve with your database, whether the items you are putting in this database typically have 10 doubles in them, or is the number 10 "just a management decision", and could they have selected 9, or 11 if they had been in a different mood?

Furthermore: are your 10 doubles really just 10 doubles, or does x[0] have a different meaning than x[1]. If you would have several strings in your name / address, would you call them AddressLine1, AddressLine2, City, PostCode, or would you call them AddressData[4]? Think whether your doubles have a special meaning, or whether they are interchangeable.

Can it be, that in a few years, when you have zillions of records, they suddenly decide they want to save 11 doubles instead of 10 doubles? Or maybe worse: some records have 10 values, some have 11 values, and some may even have a variable number of values.

If you really think, that the number 10 is not just a decision, but is intrinsic to the problem you are solving, as solid as the number of squares on a chess table, then you should add all 10 doubles as separate values to your tables, because that would be the fastest for database actions

If on the other hand, you might think that in future people might want different number of doubles, I'd say go for a one-to-many relation.

This would improve readability of your code: a "Student with this grades" is easier to read as "student with his grade for arithmetic and his grade for biology, and his grade for ...", even if you are absolutely sure that your School will only have 10 courses.

For the cost of slowing down some of your database accesses if you put your values in a one-to-many relationship, you'll have the benefits of improved reusability / maintainability (in future some items might have 11 values, or zero values), lower learning curves because it is more common to implement it as a one-to-many.

WynDiesel · Accepted Answer · 2018-02-23 09:50:32Z

1

You should keep your data row based, and not column based. For instance

CREATE TABLE MyTableX
(
    GUID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
    Value INT NOT NULL,
    SetGUID UNIQUEIDENTIFIER REFERENCES MyTableXSets(GUID)
)

CREATE TABLE MyTableXSets
(
    GUID UINQUEIDENTIFIER PRIMARY KEY NOT NULL,
    SetName VARCHAR(MAX)
)

This means you will have one entry in MyTableXSets, with linked rows in MyTableX. If you need to know in which index it was as well, you could add an Index INT on MyTableX as well.

edited Feb 23, 2018 at 9:50

answered Feb 23, 2018 at 9:00

WynDiesel

1,2141 gold badge10 silver badges43 bronze badges

Collectives™ on Stack Overflow

Best way of storing array on database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related