At work we have a C# program a machine operator use to send article data to a machine. Now they want us to adjust the program so that we can save different articles on Azure. The article data in the C# program consist of:
ArticleName (string)
Type(int)
Length (int)
Tool (array of int)
Position (array of double)
I had never looked at any database until about two weeks ago. I have successfully made a very simple test program that add two values (one int and one string) in two different columns in Azure, in a database table I created manually in SSMS. And then I have been able to retrieve the data, but that is it.
The arrays are the big thing. Depending of the article, it usually contains between 30-40 elements each, but it can vary all the way from 1 element to 200 elements.
I can change the arrays to lists in C# if that helps.
My question is: How should I design the database table(s)?
I have been searching around, and cannot understand if should make multiple tables, or use some {145.0, 186.7, 302.24} type syntax, or some other way.
Update: Thanks for the replays. I'm reading up at everything, and have now added DB version control to the list.
I estimate that once it is all said an done we will have about 1000 articles in the database. We write data only once per article when creating the article, and then I imagine a "Load" button in the HMI that lists all articles (Only "Name" and possible "Length"). The user chose one article, and all data from that article is read from the DB to the C# program. The operator might do this 10-20 times a day.
siggemannen: Do you mean something like this? Table Relations
Correlation Yes. The Tool and Position are correlated. The tool used on array element one in the Tool-array, correlates to position element one in the Position array.
About the answer from "stoos": I have not planed to use any query like "retrieve all articles with the Tool equals 4". But I have started to think what might come in the future, and maybe it is best to not restrict anything.
As the commentators already pointed, it depends. There are two options actually:
If you want to query the arrays like "retrieve all articles with the Tool equals 4", it is better to extract your Tools array to new table Tool and reference with articles.
If you are sure that you would never query your arrays the way above, then just store them as a blob column, for example as json array or just as string containing values separated by comma - what is easier for you to deserialize in your app.
