database - MySQL Table Structure - Storing a limited set of numbers -

database - MySQL Table Structure - Storing a limited set of numbers -

i need store set of numbers in mysql database. need help determine best table construction use.

there 20 numbers stored in each row, along id. numbers can range 1 - 80 , there no repeats in series of numbers.

initially created table construction 21 columns, id , 20 columns 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 thought of table 81 columns, id , 80 boolean columns 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 give advice pros , cons of each table structure, , easier utilize when searching table. example, need search every row contains 1,2,5,66, , 79. or every row contains 16,33, , 4.

any guidence appreciated.

what you're looking called database normalization; way organize info prevents duplication , anomalies (like changing 1 record inadvertently changing record).

higher-normal forms depend on meaning of data, have not told us, start should avoid ordered or indeterminate columns (like num1, num2, ...) , split columns rows:

id num 0001 1 0001 4 0001 15 ... 0002 3 0002 5 ...

in general, time find adding bunch of columns depend on position making mistake. sql has many functions aggregating, combining, sorting, , reporting on rows. utilize features of sql produce results want; don't seek create database schema final printed report.

in reply comment, query returns ids have nums 1, 4, , 15, , no other id:

select id yourtable num in (1, 4, 15) grouping id having count(id) = 3

if nums can duplicated want having count(distinct id). if can have different counts of nums match have create temporary table of nums match , utilize having count(id) = (select count(num) temporarytable).

note sql server has master..spt_values table of integers utilize in such situations; not know if mysql has such thing, easy generate if need one.

mysql database table