3

I'm currently developing a software with C# and want it to store the data in a database. My problem is that i'm looking for the best approach to store the data of an object that contains two arrays. The objects in the array look exactly the same but they got a different meaning.

As an information: The data of the objects in the data is changing regularly.

For example i got the following two classe:

public class ObjectA
{
    public string Text { get; set; }
    public int Value { get; set; }
}

public class ObjectB
{
    public string Text { get; set; }
    public int Value { get; set; }
}

public class ObjectC
{
    public string Name { get; set; }
    public List<ObjectA> DetailsA { get; set; }
    public List<ObjectB> DetailsB { get; set; }
}

Please note that i currently got over 24000 objects from ObjectC. The amount of objects that each array of those objects contains can vary quite a lot (up to 200 and maybe more in the future). Could this lead to any problems with the maximum row count if i use one of the solutions?

I got the following two ideas of how the database shema could look like:

  1. All attributes in one table.

    Create a coloumn for each attribute of ObjectA and ObjectB so that i can store each object in the array in one row.

    CREATE TABLE `data` (
  `data_id`     INT            NOT NULL,
  `name_a`      NVARCHAR(50)   NOT NULL,
  `text_a`      NVARCHAR(100)  NOT NULL,
  `name_b`      NVARCHAR(50)   NOT NULL,
  `text_b`      NVARCHAR(100)  NOT NULL,
  `value`       INT            NOT NULL,
  PRIMARY KEY (`data_id`)
)

    In this case i would store the value of name redundantly.

  2. Creating a foreign key in the table for ObjectA

    By doing this i could avoid storing the data from ObjectC redundantly while having to use joins when querying the data.

    CREATE TABLE `data` (
  `data_id`     INT            NOT NULL    AUTO_INCREMENT,
  `name`        NVARCHAR(50)   NOT NULL,
  PRIMARY KEY (`data_id`)
)

CREATE TABLE `details_a` (
  `a_id`        INT            NOT NULL    AUTO_INCREMENT,
  `text`        NVARCHAR(100)  NOT NULL,
  `value`       INT            NOT NULL,
  `data_fk`     INT            NOT NULL,
  PRIMARY KEY (`a_id`)
)

CREATE TABLE `details_b` (
  `b_id`        INT            NOT NULL    AUTO_INCREMENT,
  `text`        NVARCHAR(100)  NOT NULL,
  `value`       INT            NOT NULL,
  `data_fk`     INT            NOT NULL,
  PRIMARY KEY (`b_id`)
)

Also, would it be better to create a new row in the database for each time the data of the arrays has changed, or should I change the existing data?

Edit: Added some information about the amount of objects (right below the c# example).

Improve this question
3
  • 4
    As a third way you could use a descriminator-column and store A and B in the same dbtable - the discriminator tells you if its a A or a B. The normal/lazy approach is to use / configure an orm mapper to do this for you so you can treat your DB as C# objects and let the details be handled by the orm mapper (google EntityFramework, Hibernate, code-first, data-first, f.e. here: msdn.microsoft.com/en-us/library/jj200620(v=vs.113).aspx ). Approach 1 does not work with lists of As or Bs btw - you need at least your 2nd version with fks. Commented Jan 4, 2018 at 23:51
  • Encode it in JSON, then put it into a TEXT column. Commented Jan 5, 2018 at 2:26
  • What's the advantage of storing the data as json? Commented Jan 5, 2018 at 2:43

2 Answers 2

Reset to default
2

(moved from comment to answer, got too long)

Approach 1 does not work with lists of As or Bs btw - you need at least your 2nd version with fks.

Using joins is good, database normalization is good as well. It partitions your data and allows the DB to optimize queries more better.

As a third way you could use a descriminator-column and store A and B in the same dbtable - the discriminator tells you if its a A or a B. 

CREATE TABLE `details_ab` (
  `a_id`        INT            NOT NULL    AUTO_INCREMENT,
  `text`        NVARCHAR(100)  NOT NULL,
  `value`       INT            NOT NULL,
  'isA'         BIT            NOT NULL,   -- True == A else B 
  `data_fk`     INT            NOT NULL,
  PRIMARY KEY (`a_id`)
)

The normal/lazy approach is to use / configure an orm mapper to do this for you so you can treat your DB as C# objects and let the details be handled by the orm mapper (google EntityFramework, Hibernate, code-first, data-first, f.e. here: msdn.microsoft.com/en-us/library/jj200620(v=vs.113).aspx ).

As for the insert or update - it depends. If you have to (legally f.e. or for insurance) have to have the complete train of data in your DB you could use a table and ever only insert - and a view on top, that diplays the "newest" version of a dataitem.

i.e. every row has a ID and you have a "BusinessID" that marks "an object" uniquely for you - you insert it the first time, then you modify it: this inserts it with a new DB-Id and the same "BusinessID". You create a view on the table that only shows the highest DB-ID of each BusinessID (assuming integer DB-Ids). You have the whole history in your table, but your application "normally" only sees the view, but maybe admins might get access to the whole table instead of the view ... etc.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Another benefit of the discriminator-column method discussed here is flexibility for the future. If you want to add a third detail-type in the future, this method makes such a change rather trivial. As a nitpick re ORM mappers, I wouldn't say they're the "lazy" approach. To use them properly, you really need to understand your schema first, so I don't think they simplify database design work; the ORM mapper just makes life a lot easier when conversing between the data layer and the business layer. A poorly-designed schema is a nightmare, ORM or not.
I forgot to mention how many objects i need to handle (don't know how relevant this information is but just in case). I have edited my post accordingly.
if you havemany big tables, add more ram and ssds to your server - below some million rows I would not spend much thought about it. finding stuff in long tables is what dbs do.using
0

Creating the table structure with foreign keys is probably the best solution, and it would be the most commonly implemented. That said, I know needs can vary based on usage case, so here's one possible alternative.

Depending on the usage and the size, I've found it helpful to store objects and variables as XML serialized equivalents in the DB. If you just need it for persistent storage, that's the simplest method and then you could save versioning with a simple table holding a timestamp and the XML object. In a pinch, you can even query your XML objects with SQL to access the data or migrate the storage method if needs change later. I use this method for storing dictionary style data with different data types in a single table for quick access to environmental variables.

Improve this answer

1 Comment

I ve never played with XML in Sql (beside the usual STUFF .. FROM XML needed in 2008r2 earlier for grouping texts. Can you join/avg/sum etc over xmldata performantly?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.