Search code examples
mysqldatabase-normalization

How to normalize a table with similar column names?

I have an SQL table like so:

column(id, name, title, view1, view2, view3, view4);

table that lists columns

where the view1, view2,... are boolean fields indicating whether or not a specific table column with name is shown or hidden, when a corresponding viewX is selected.

For example, consider a table with column names like (time, pressure, efficiency, ...) and various data (see below for definition of "column")

time  | pressure | efficiency
----------------------------
00:00 |    53.3  |    39%
00:01 |    53.3  |    39%
00:03 |    53.3  |    39%

I wish to select view1 display mode, where view1 is to ONLY show time column. Then I may wish to select view2 display mode where I ONLY want to see time and pressure columns. view3 is to show only time and efficiency. view4 shows all the column names and data (time, pressure, efficiency).

How can I normalize this table? I am having difficulty figuring out which direction to go. Do I need to normalize it in the first place?

Terminology

To use clear terminology, I will use the word "column" to indicate my HTML table columns use case, where column is (time, pressure, efficiency, etc), and the word "field" to indicate SQL database field.

P.S. My answer seems to be to create a relation between the column table and the group table, where column contains values like (time, pressure, efficiency), and group contains (view1, view2, view3, etc). Based on relation, if relation database table contains a row with (view X, fieldname Y) then the fieldname Y is to be displayed when view X is chosen. I think it is my answer.

Solution

  • You could create a seperate table holding the booleans:

    first_table: column(id, name, title, sort_order);

second_table: column(id, first_table_id, view_no, shown);

    here the column shown holds the booleans.