Hi timvw, you're right 'bout that, and that's the way I use to store and retrive info from my db, but I like to try different ways to do the same thing, just to study, and ...may be........ and I say may be, this process would be usefull to retrive filtered data from a table with unknown amount of information but a well known number of record, for example a table with not more than 2000 records, but for example "the example I wrote before" an Unknown number of peaks for record, this is an exercise to store the most significant peaks (on the Spectrum Analysis) on a set of audio samples used to generate wave model. In this case I can't make a table with a huge number of columns just to assign a value to an specific frequency, i prefere to use a colum for the most important peaks in frequence and a column for the values in decibels.
By doing this I just don't need to know in advance how many coumns I need to save, just split the info
Code: Select all
+----------------------+------------------------+
| Hz | decibels |
+----------------------+------------------------+
|200-500-1358 |30-15-5 |
+----------------------+------------------------+
the other way could be to use 2 tables, one for the sample and one for the values like this
Code: Select all
-- Create the sample names with an indexed id
-- --------------------------------------------------------
CREATE TABLE `sample_index` (
`s_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`s_name` VARCHAR( 255 ) NOT NULL ,
`s_date` DATETIME NOT NULL ,
PRIMARY KEY ( `s_id` ) ,
INDEX ( `s_date` )
);
-- --------------------------------------------------------
-- then create atable for the data with 4 indexes
-- 1 - the id
-- 2 - the parent sample as a foreing index referenced to the sample_index table
-- 3 - the hz index
-- 4 - the db index
-- --------------------------------------------------------
CREATE TABLE `sample_data` (
`d_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`d_parent` INT UNSIGNED NULL ,
`d_hz` DECIMAL( 12, 4 ) NOT NULL ,
`d_decibels` DECIMAL( 12, 4 ) NOT NULL ,
PRIMARY KEY ( `d_id` ) ,
INDEX ( `d_parent`),
INDEX ( `d_hz` ),
INDEX ( `d_decibels` )
)
-- --------------------------------------------------------
-- then if for example I would like to know the name of the samples that match a significant
-- peak on the 20Hz I'd make a query like this
-- --------------------------------------------------------
SELECT `s_id`,`s_name` FROM `sample_index` WHERE `s_id` IN (SELECT `d_parent` FROM `sample_data` WHERE `d_hz` ='20')
do you think this is better for small record's db?
please give me your opinion timvw.
by tnx for your answers guys
