Need suggestions / expert opinions about saving strategy of my data

Here is the grid I’m taking about and the steps Iam performing:

I’m moving blue boxes from left to right (on the grid) after clicking Move Text Content! button. Here are the steps I follow:

  1. Select one option from Select options dropdown.
  2. Select a color from the dropdown.
  3. Click Show Options button
  4. Enter a row and column (For example A and 1) and then click calculate
  5. Hit the Move Text Content! button to move blue boxes on the grid.

I will be eventually saving all the grid-related details (I’ll have a save button somewhere on top of the grid which will achieve this) in a database table and I am yet to design the table. I am wondering if anyone has any suggestions on the same like what columns I should consider and what should I consider saving.

Here are some of my thoughts:

  1. Since I’ll be saving the contents inside the grid, I will have to have a column for storing the values of the divs (blue text box content which goes on the grid) like Option#1, Option#2 etc.
  2. Since I am also having different colors on the grid. I believe I should be storing the CSS for those specific divs as well. Not sure if saving CSS info sounds reasonable in terms of database design.
  3. I might want to save the cell value which drives the pattern.

Is there anything anyone could think of that could be worth considering to save or something I should avoid and handle differently from the above-mentioned points? Thanks!

You should never store a “calculated” value. So what you store are only the values of your input fields. All the display which results of the select should be newly calculated ans shown after loading the values.

Thanks. In my case, whatever gets saved after clicking the button, the user would like to retrieve the same, say 6 months later, and work on putting more information on the grid based on the past information. It’s like someone wants to resume their work at it later. Hence I am only interested in saving what’s on the grid.

There’s also going to be a case where the user is going to edit the things on the grid on the fly so once they are already on the grid, it can change.

Based on your suggestion, I could store every user input once they hit Move Text Content button.

Any idea how to better handle the things getting updated on the fly once they are on the grid? I will enable the edit functionality on each grid square to achieve this. Thanks!

1 Like

I think it is important that you design the table first. Use of terminology like blue boxes and the values of the divs indicate you are thinking in terms of the UI and I think that is the reverse of what you need to be thinking. The best design would start with the data.

A symptom of the preceding is that you do not make it clear how static the data will be. Assuming the data will be static and the only thing that changes is:

  • the data that can be calculated
  • whether the item is on left or the right on the grid

Then I think that simplifies things. You do not need to store data that can be calculated, as Thallius said. Then you only need a field (column) that indicates which side the item is on.

I am not sure but nearly sure that HTML supports drag-and-drop. If the number of items is small (perhaps with the aid of filters, as I think you are doing) enough then that might help.

Thanks for your valuable input. I was thinking about the same.

In terms of thinking, I came across these table columns I could create in my database table:

  1. id which will be the primary key of the table for each record set to autoincrement.

  2. options column which will store the value selected from the Select Options dropdown.

  3. color column to store the color.

  4. row column to store values from A-H

  5. numericalVaue column to store values from 1-12

So once user hits Move the Text Content!, I can save these values in the database table.

One thing is puzzling me is that if I should have options column in a separate table as user can keep on filling the grid again and again by selecting different values from the input fields. Any one has any thoughts?


I am not sure what you are asking.

Assume you have the following select:

<option value="1">Capricorn</option>
<option value="2">Aquarius</option>
<option value="3">Pisces</option>
<option value="4">Aries</option>
<option value="5">Taurus</option>
<option value="6">Gemini</option>
<option value="7">Cancer</option>
<option value="8">Leo</option>
<option value="9">Virgo</option>
<option value="10">Libra</option>
<option value="11">Scorpio</option>
<option value="12">Sagittarius</option>

You should have a table with a primary key of the values from the value attribute. Then in your main table you would only store the value from the value attribute, not the text. The value would be considered a foreign key. There are very many articles about that type of thing. It is very basic to database design.

Thanks. So something like the following - just elaborating your suggestion.

Let’s say I have a table called OptionsTable where all the values are saved from your example select. So it will look like this where valueID is the primary key in this table.


And then I have another table, let’s say SelectedOptions, where valueID is the foreign key to the primary key valueID column of OptionsTable.

So for example, if I selected the option #1 which is Capricon and if that selection produces four results in the HTML like this:

 <div data-id="43"><span class="words" style="color:black;background-color:white" data-id="0">Capricon#1</span></div>

<div data-id="44"><span class="words" style="color:black;background-color:white" data-id="0">Capricon#2</span></div>

<div data-id="45"><span class="words" style="color:black;background-color:white" data-id="0">Capricon#3</span></div>

<div data-id="46"><span class="words" style="color:black;background-color:white" data-id="0">Capricon#4</span></div>

So I am going to store the values 43,44,45 and 46 inside the selectedValueID column of the SelectedOptions table like the following:


Is the overall table design and how the inserted data is going to live inside both table looking reasonable?

Thanks again for your time!

The options table would have at least two columns. Its primary key would be the id, the value used internally and the value used for the value attribute. Such as the numbers 1 to 12. The other column would be the text that people typically see. In database theory the idea is to eliminate duplication. In other words use the value 1 instead of Capricorn in a table such as a table of people.

OK, so for example, let’s say, Capricorn is selected.

<option value="101">Capricorn</option>
<option value="102">Aquarius</option>
<option value="103">Pisces</option>
<option value="104">Aries</option>
<option value="105">Taurus</option>
<option value="106">Gemini</option>
<option value="107">Cancer</option>
<option value="108">Leo</option>
<option value="109">Virgo</option>
<option value="110">Libra</option>
<option value="111">Scorpio</option>
<option value="121">Sagittarius</option>

And it produces same four HTML results as described in my post #7. So the OptionsTable would look like the following after hitting the move button (here id column is the primary key and valueID is the value of Capricon:


I was thinking about this in terms of adding a third column in OptionsTable but since there are four values generated after selecting Capricon, I was not sure if it’s possible in this table.

So I guess the SelectedOptions will table look like this (optionsID is the primary key of the SelectedOptions table :

I believe I have messed up the foreign key related thing here in above table. Am I heading in wrong direction as far as above two tables are concerned? Thanks!

It would be better for you to find articles about databases. There are hundreds, probably thousands of articles written by others. Perhaps a book about developing websites using databases and your chosen server-side language (such as PHP) would help.