CODEWITHSUNDEEP
mysqldatabasedatabase-design
user3780155
user3780155

Reputation: 1

MySQL Table Structure - Storing a limited set of numbers

I need to store a set of numbers in a MySQL database. I need some help to determine the best table structure to use.

There are 20 numbers that will be stored in each row, along with an ID. The numbers can range from 1 - 80 and there are no repeats in this series of numbers.

Initially I created a table structure with 21 columns, an ID and 20 columns that store each individual number. 

Id     | Num1  | Num2  | Num3  | Num4  | Num5  |  etc..  |
----------------------------------------------------------
0001   |   1   |   4   |  15   |  22   |  39   |  43     |
0002   |   3   |   5   |  22   |  43   |  55   |  58     |
0003   |   1   |   3   |  5    |  6    |  15   |  26     |

I've also thought of a table with 81 columns, an ID and 80 boolean columns that would represent each individual number. 

Id     |   1   |   2   |   3   |   4   |   5   |  etc..  |
----------------------------------------------------------
0001   | True  | False | False | True  | True  | False   |
0002   | False | False | True  | False | True  | False   |
0003   | True  | False | True  | False | True  | True    |

Can anyone give some advice to the pros and cons of each table structure, and which would be easier to use when searching this table. For example, we would need to search for every row that contains 1,2,5,66, and 79. Or every row that contains 16,33, and 4.

Any guidence would be appreciated.

Upvotes: 0

Views: 124

Answers (1)

Dour High Arch
Dour High Arch

Reputation: 21722

What you're looking for is called database normalization; a way to organize data that prevents duplication and anomalies (like changing one record inadvertently changing another record).

Higher-normal forms depend on the meaning of your data, which you have not told us, but to start you should avoid ordered or indeterminate columns (like Num1, Num2, ...) and split your columns into rows:

  ID  Num
0001    1
0001    4
0001   15
...
0002    3
0002    5
...

In general, any time you find yourself adding a bunch of columns that depend on their position you are making a mistake. SQL has many functions for aggregating, combining, sorting, and reporting on rows. Use the features of SQL to produce the results you want; don't try to make your database schema look like the final printed report.

In answer to your comment, a query that returns only IDs that have Nums 1, 4, and 15, and no other ID:

select ID from YourTable
where Num in (1, 4, 15)
group by ID
having Count(ID) = 3

If Nums can be duplicated you will want something like having count(distinct ID). If you can have different counts of Nums to match you will have to create a temporary table of Nums to match and use having count(ID) = (select Count(Num) from TemporaryTable).

Note that SQL Server already has a master..spt_values table of integers to use in such situations; I do not know if MySql has such a thing, but they are easy to generate if you need one.

Upvotes: 1

Related Questions