database design - Vertical partitioning of composite keys -

database design - Vertical partitioning of composite keys -

i have track values combination of different resources everyday. table might like:

create table `data` ( `id` int unsigned null primary key auto_increment, `datetime` datetime not null, `res1` int unsigned not null, `res2` int unsigned not null, `res3` int unsigned not null, `res4` int unsigned not null, `res5` int unsigned not null, `value` decimal(10,0) not null, unique index `datetime_res1_to_res5` (`datetime`, `res1`, `res2`, `res3`, `res4`, `res5`) )

where res1 through res5 foreign keys respective tables.

this table contains lot of rows--will crack 20 million.

what i'm curious of if should set combination of foreign keys separate table, such have 2 tables so:

create table `data` ( `id` int unsigned null primary key auto_increment, `datetime` datetime not null, `superkeys_id` int unsigned not null, `value` decimal(10,0) not null, unique index `datetime_superkeys_id` (`datetime`, `superkeys_id`) ) create table `superkeys` ( `id` int unsigned null primary key auto_increment, `res1` int unsigned not null, `res2` int unsigned not null, `res3` int unsigned not null, `res4` int unsigned not null, `res5` int unsigned not null, unique index `res1_to_res5` (`res1`, `res2`, `res3`, `res4`, `res5`) )

where data.superkeys_id foreign key superkeys.id.

this cut down size of table. i'm not sure if might bad thought reasons not know. selects require bring together breakdown of data, add together little more overhead, shouldn't think problem.

in real world situation, 1 of resources user_id , need sum values user, maintain such column in data rather create part of superkeys table sake of not having bring together on every query. utilize bring together when need sum values other resources less often.

it won't cut down size of data. you'll have store 20 1000000 rows of info in 1 table, , 20 1000000 rows of superkeys in other.

five integers 40 bytes. multiply 20 million--800 megabytes, plus datetime column , decimal. whole table fit in ram on netbook.

keep table "data". drop surrogate key.

database-design composite-key