No announcement yet.

Data Normalization Questions

  • Filter
  • Time
  • Show
Clear All
new posts

  • Data Normalization Questions

    I have a few apps that store multiple values in a single field and would like to look into normalizing the data. I am starting to work on analysis graphs for the data, and with the multiple values, it is difficult to do that.

    Example: I have a field "things" that has a value stored as "1;10;12;16". The values are semi-delimited and refer keys on a different table for the labels of those items.

    I think to better analyze the data, I should break the field "things" out to a different table. The new table would use the record ID as the foreign key. Each value would be a new line.

    Does anyone know of any examples of this being done? I can wrap my head around a few things, but I am missing some basics, such as how to refer to that new table for building a menu on a form and selecting the correct values. SC breaks apart the semi-delimited values easily, but I can't seem to find how to do something like I feel I should do.

    Also, is my thought about breaking the semi-delimited values to a new table warranted? Is there a better way to treat multiple value fields?

    Thanks in advance.

  • #2
    Not any easy topic to explain. You have done well.


    • #3
      I suggest a OneToMany approach so you can create "DetailTable" that store every value in single rows. "DetailTable" must have at least 3 fields: Id ( Primary Key) , MasterId ( Foreign Key ), and Value
      In this case it's easy to manage with SC
      For the second question it's important to know wich DB are you using. For sure there is a function to split a single delimited field into a multi-record structure
      Giorgio Bravi
      Dolphin Software & Thinkware