Fetching Multilingual And Relational Data

[boen_robot]

I'm making a product catalog for a local company, but I want to reuse that DB schema for future similar projects.Even though this client doesn't really want a multilingual site right now, many others do, and besides, I'm being ready in case he eventually decides to go that way.To combine both portability and internationalization, I have the DB with several tables:- Two category tables, each holding the name of the category in its respective language. (bg/en_categories)- Two description tables, each holding the name and description of an item in its respetive language. (bg/en_item_description)- One item table, which references the category's ID, and containing the price of the item. (items)(here's where it gets complex)- Two tables with numeric characteristics, each containing the name of a numerical characteristic in the respective language, and it's suffix. (bg/en_characteristics_numeric)- One table for the values of the numeric characteristic of an item, which references an item and a characteristic, while specifying the value for that characteristic. (item_characteristic_sets_numeric)Now, I already have a SELECT statement that gets all, some or one item with, displays its category, name, description, price, and even an image associated with it (from the images table and with each image being associated with the item_image_sets table). It looks something like:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';CREATE SCHEMA IF NOT EXISTS `COMPANY` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;SHOW WARNINGS;USE `COMPANY`;-- ------------------------------------------------------- Table `COMPANY`.`bg_categories`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`bg_categories` (  `categoryID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `category` VARCHAR(45) NOT NULL ,  PRIMARY KEY (`categoryID`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`en_categories`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`en_categories` (  `categoryID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `category` VARCHAR(45) NOT NULL ,  PRIMARY KEY (`categoryID`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`items`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`items` (  `itemID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `categoryID` INT UNSIGNED NOT NULL ,  `price` DOUBLE NOT NULL ,  PRIMARY KEY (`itemID`) ,  CONSTRAINT `fk_items_bg_categories`    FOREIGN KEY (`categoryID` )    REFERENCES `COMPANY`.`bg_categories` (`categoryID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION,  CONSTRAINT `fk_items_en_categories`    FOREIGN KEY (`categoryID` )    REFERENCES `COMPANY`.`en_categories` (`categoryID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION)ENGINE = InnoDB;SHOW WARNINGS;CREATE INDEX fk_items_bg_categories ON `COMPANY`.`items` (`categoryID` ASC) ;SHOW WARNINGS;CREATE INDEX fk_items_en_categories ON `COMPANY`.`items` (`categoryID` ASC) ;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`images`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`images` (  `imageID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `location` VARCHAR(45) NOT NULL ,  PRIMARY KEY (`imageID`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`image_tags`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`image_tags` (  `tagID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `tag` VARCHAR(45) NOT NULL ,  PRIMARY KEY (`tagID`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`bg_item_description`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`bg_item_description` (  `itemID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `name` VARCHAR(45) NOT NULL ,  `description` TEXT NOT NULL ,  PRIMARY KEY (`itemID`) ,  CONSTRAINT `fk_bg_item_description_items`    FOREIGN KEY (`itemID` )    REFERENCES `COMPANY`.`items` (`itemID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION)ENGINE = InnoDB;SHOW WARNINGS;CREATE INDEX fk_bg_item_description_items ON `COMPANY`.`bg_item_description` (`itemID` ASC) ;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`en_item_description`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`en_item_description` (  `itemID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `name` VARCHAR(45) NOT NULL ,  `description` TEXT NOT NULL ,  PRIMARY KEY (`itemID`) ,  CONSTRAINT `fk_en_item_description_items`    FOREIGN KEY (`itemID` )    REFERENCES `COMPANY`.`items` (`itemID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION)ENGINE = InnoDB;SHOW WARNINGS;CREATE INDEX fk_en_item_description_items ON `COMPANY`.`en_item_description` (`itemID` ASC) ;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`item_image_sets`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`item_image_sets` (  `imageSetID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `itemID` INT UNSIGNED NOT NULL ,  `imageID` INT UNSIGNED NOT NULL ,  PRIMARY KEY (`imageSetID`) ,  CONSTRAINT `fk_item_image_sets_images`    FOREIGN KEY (`imageID` )    REFERENCES `COMPANY`.`images` (`imageID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION,  CONSTRAINT `fk_item_image_sets_items`    FOREIGN KEY (`itemID` )    REFERENCES `COMPANY`.`items` (`itemID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION)ENGINE = InnoDB;SHOW WARNINGS;CREATE INDEX fk_item_image_sets_images ON `COMPANY`.`item_image_sets` (`imageID` ASC) ;SHOW WARNINGS;CREATE INDEX fk_item_image_sets_items ON `COMPANY`.`item_image_sets` (`itemID` ASC) ;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`image_tag_sets`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`image_tag_sets` (  `imageTagID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `imageID` INT UNSIGNED NOT NULL ,  `tagID` INT UNSIGNED NOT NULL ,  PRIMARY KEY (`imageTagID`) ,  CONSTRAINT `fk_image_tag_sets_images`    FOREIGN KEY (`imageID` )    REFERENCES `COMPANY`.`images` (`imageID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION,  CONSTRAINT `fk_image_tag_sets_image_tags`    FOREIGN KEY (`tagID` )    REFERENCES `COMPANY`.`image_tags` (`tagID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION)ENGINE = InnoDB;SHOW WARNINGS;CREATE INDEX fk_image_tag_sets_images ON `COMPANY`.`image_tag_sets` (`imageID` ASC) ;SHOW WARNINGS;CREATE INDEX fk_image_tag_sets_image_tags ON `COMPANY`.`image_tag_sets` (`tagID` ASC) ;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`bg_characteristics`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`bg_characteristics` (  `characteristicID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `characteristic` VARCHAR(45) NOT NULL ,  `suffix` VARCHAR(45) NOT NULL ,  PRIMARY KEY (`characteristicID`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`en_characteristics`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`en_characteristics` (  `characteristicID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `characteristic` VARCHAR(45) NOT NULL ,  `suffix` VARCHAR(45) NOT NULL ,  PRIMARY KEY (`characteristicID`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`bg_item_characteristics_values`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`bg_item_characteristics_values` (  `characteristicSetID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `value` VARCHAR(180) NOT NULL ,  PRIMARY KEY (`characteristicSetID`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`en_item_characteristics_values`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`en_item_characteristics_values` (  `characteristicSetID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `value` VARCHAR(180) NOT NULL ,  PRIMARY KEY (`characteristicSetID`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`item_charactestic_sets`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`item_charactestic_sets` (  `characteristicSetID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `itemID` INT UNSIGNED NOT NULL ,  `characteristicID` INT UNSIGNED NOT NULL ,  PRIMARY KEY (`characteristicSetID`) ,  CONSTRAINT `fk_item_charactestic_sets_items`    FOREIGN KEY (`itemID` )    REFERENCES `COMPANY`.`items` (`itemID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION,  CONSTRAINT `fk_item_charactestic_sets_bg_characteristics`    FOREIGN KEY (`characteristicID` )    REFERENCES `COMPANY`.`bg_characteristics` (`characteristicID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION,  CONSTRAINT `fk_item_charactestic_sets_en_characteristics`    FOREIGN KEY (`characteristicID` )    REFERENCES `COMPANY`.`en_characteristics` (`characteristicID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION,  CONSTRAINT `fk_item_charactestic_sets_bg_item_characteristics_values`    FOREIGN KEY (`characteristicSetID` )    REFERENCES `COMPANY`.`bg_item_characteristics_values` (`characteristicSetID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION,  CONSTRAINT `fk_item_charactestic_sets_en_item_characteristics_values`    FOREIGN KEY (`characteristicSetID` )    REFERENCES `COMPANY`.`en_item_characteristics_values` (`characteristicSetID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION)ENGINE = InnoDB;SHOW WARNINGS;CREATE INDEX fk_item_charactestic_sets_items ON `COMPANY`.`item_charactestic_sets` (`itemID` ASC) ;SHOW WARNINGS;CREATE INDEX fk_item_charactestic_sets_bg_characteristics ON `COMPANY`.`item_charactestic_sets` (`characteristicID` ASC) ;SHOW WARNINGS;CREATE INDEX fk_item_charactestic_sets_en_characteristics ON `COMPANY`.`item_charactestic_sets` (`characteristicID` ASC) ;SHOW WARNINGS;CREATE INDEX fk_item_charactestic_sets_bg_item_characteristics_values ON `COMPANY`.`item_charactestic_sets` (`characteristicSetID` ASC) ;SHOW WARNINGS;CREATE INDEX fk_item_charactestic_sets_en_item_characteristics_values ON `COMPANY`.`item_charactestic_sets` (`characteristicSetID` ASC) ;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`en_characteristics_numeric`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`en_characteristics_numeric` (  `characteristicID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `characteristic` VARCHAR(45) NOT NULL ,  `suffix` VARCHAR(45) NOT NULL ,  PRIMARY KEY (`characteristicID`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`bg_characteristics_numeric`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`bg_characteristics_numeric` (  `characteristicID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `characteristic` VARCHAR(45) NOT NULL ,  `suffix` VARCHAR(45) NOT NULL ,  PRIMARY KEY (`characteristicID`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `COMPANY`.`item_characteristic_sets_numeric`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `COMPANY`.`item_characteristic_sets_numeric` (  `characteristicSetID` INT NOT NULL AUTO_INCREMENT ,  `itemID` INT UNSIGNED NOT NULL ,  `characteristicID` INT UNSIGNED NOT NULL ,  `value` DOUBLE NOT NULL ,  PRIMARY KEY (`characteristicSetID`) ,  CONSTRAINT `fk_item_characteristics_sets_numeric_items`    FOREIGN KEY (`itemID` )    REFERENCES `COMPANY`.`items` (`itemID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION,  CONSTRAINT `fk_item_characteristics_sets_numeric_bg_characteristics_numeric`    FOREIGN KEY (`characteristicID` )    REFERENCES `COMPANY`.`bg_characteristics_numeric` (`characteristicID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION,  CONSTRAINT `fk_item_characteristics_sets_numeric_en_characteristics_numeric`    FOREIGN KEY (`characteristicID` )    REFERENCES `COMPANY`.`en_characteristics_numeric` (`characteristicID` )    ON DELETE NO ACTION    ON UPDATE NO ACTION)ENGINE = InnoDB;SHOW WARNINGS;CREATE INDEX fk_item_characteristics_sets_numeric_items ON `COMPANY`.`item_characteristic_sets_numeric` (`itemID` ASC) ;SHOW WARNINGS;CREATE INDEX fk_item_characteristics_sets_numeric_bg_characteristics_numeric ON `COMPANY`.`item_characteristic_sets_numeric` (`characteristicID` ASC) ;SHOW WARNINGS;CREATE INDEX fk_item_characteristics_sets_numeric_en_characteristics_numeric ON `COMPANY`.`item_characteristic_sets_numeric` (`characteristicID` ASC) ;SHOW WARNINGS;SET SQL_MODE=@OLD_SQL_MODE;SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

[justsomeguy]

Product neutrality is the problem, if you could store the characteristic values in the items table that would obviously make a lot of things easier.I'm not sure if there's a good way to do this other than to use something like a stored procedure. The main issue is that the characteristic values are each a single row. If you're doing a join to get your item and all values for it, that result set might have 20 rows if the item has 20 values (with the record from the item table duplicated for each characteristic row). Moreover, if you're looping through a result set with several items in it, each with several rows of options, you'll have to keep track which item you're on in case one of them is missing a value (e.g., one of the items only has 19 rows and the rest have 20).I'm trying to think of where I saw an application using a generic DB structure like this, it might be Gallery2. This probably isn't what you want to hear, but I'm thinking that the tradeoff of using a generic DB is that more queries are required to get the information. One of the main advantages of tailoring the DB to a particular application is that you can get a lot of relevant information with a single query.

[boen_robot]

Product neutrality is the problem, if you could store the characteristic values in the items table that would obviously make a lot of things easier.

Yeah, I realize that one... like what I have for the price. Every item in every catalog has a price (even if it's free, in which case a plain 0 does the trick). However, different kinds of products have different characteristics, and some have unique ones.I realize I may add a new column for every characteristic, but then how do I let the user create new characteristics? I can always store the names and suffixes in a separate table (one for each language), but... wait just a sec... is there a way that a column can be added to a table? And is there a way (in one query) it can be removed after a check that all values in it are NULL (i.e. no item has this characteristic anymore)? Will it make the DB heavier, in terms of its size? Note also that often some characteristics apply only for a certain category (e.g. if they sell computers and cars, fuel consumption applies only to cars while CPU frequency applies only to computers).

I'm not sure if there's a good way to do this other than to use something like a stored procedure. The main issue is that the characteristic values are each a single row. If you're doing a join to get your item and all values for it, that result set might have 20 rows if the item has 20 values (with the record from the item table duplicated for each characteristic row). Moreover, if you're looping through a result set with several items in it, each with several rows of options, you'll have to keep track which item you're on in case one of them is missing a value (e.g., one of the items only has 19 rows and the rest have 20).

OK... let it be a stored procedure... but with what in it? It still needs to be in one result set (even if it has to be large and filled with NULLs due to a characteristic being applicable only to one item in the resultset).

I'm trying to think of where I saw an application using a generic DB structure like this, it might be Gallery2. This probably isn't what you want to hear, but I'm thinking that the tradeoff of using a generic DB is that more queries are required to get the information. One of the main advantages of tailoring the DB to a particular application is that you can get a lot of relevant information with a single query.

Yeah, I was afraid of that... but the stuff that most of my (currently potential) clients want is basically an e-commerce app, only without the e-commerce part (i.e. no shopping online, but with contacts information and a catalog). The only differences are in the characteristics of the offered products (and the actual products of course).

[justsomeguy]

is there a way that a column can be added to a table

Sure, ALTER TABLE does that, for adding and removing. I'm not sure if there's a way to conditionally remove a column only if it's null, but there might be.

Will it make the DB heavier, in terms of its size?

A little bit, the main thing with adding and removing columns is speed though. If there are many records in the table it's going to take longer to add a new column. It can still be measured in terms of a couple seconds though, it's just a little bit longer. Heck, I changed a table with ~300k rows from MyISAM to InnoDB and that only took a couple seconds, so adding a new column shouldn't be all that expensive.

OK... let it be a stored procedure... but with what in it?

That I'm not sure, I haven't made enough SPs to be able to answer that. I do know that you can essentially build your own recordset in the SP though, so you can build a recordset with a single record with all the value columns, even if it takes you several queries to gather all that data. SPs have a sort-of "return" statement where you say what recordset you want to return. Unfortunately, my main experience with SPs and user-defined functions is with SQL Server, so I'm not sure how different that is than MySQL. That might be your answer though, that would mean that setting up new sites would primarily involve editing the SPs to match that site's data.

[boen_robot]

With alter table at hand, I guess I could create a stored procedure for removal.But still, how do I add category specific characteristics? As far as item specific go, I'm willing to accept the NULLs that will arise when an item doesn't have the characteristic, but category specific ones? Maybe with a separate table named like *categoryID*_characteristics with columns like itemID, characteristic_*name* (+ more characteristic_*name* columns for each category specific one).My current template system can't iterate over arbitrary columns (I instead have direct 1:1 mappings with the result set - this goes here, that goes there), but that's another story I'll be working on.Thanks. I guess that helps. I'll create a separate table for each category containing that category's characteristics. It will also include the item specific characteristics (as it's usually not long before two or more products with the same thing appear). The actual extraction from the result set is another thing I'll bash my head on a little. Thanks again.

[justsomeguy]

Let me also tell you what we're doing here. We've got a system where one part is user management. We've got so many different customers using this thing, that it's just not possible to create one set of fields that will apply to any user, and it's also not feasible to change the database structure every time we need to set up a new installation. The solution we use, and I'm not saying this is the best, is to limit how many fields people are allowed to add. We happen to limit that at 30 fields. So, the users table looks like this:

CREATE TABLE `users` (  `id` int(10) unsigned NOT NULL auto_increment,  `username` varchar(50) collate utf8_unicode_ci NOT NULL,  `password` varchar(40) collate utf8_unicode_ci NOT NULL,  `fname` varchar(255) collate utf8_unicode_ci default NULL,  `lname` varchar(255) collate utf8_unicode_ci default NULL,  `email` varchar(255) collate utf8_unicode_ci default NULL,  `active` tinyint(1) unsigned NOT NULL,  `date_registered` int(10) unsigned NOT NULL,  `last_login` int(10) unsigned NOT NULL,  `last_ip` varchar(15) collate utf8_unicode_ci default NULL,  `pw_reset_token` varchar(20) collate utf8_unicode_ci default NULL,  `pw_reset_time` int(10) unsigned default NULL,  `pw_reset_ip` varchar(15) collate utf8_unicode_ci default NULL,  `pw_last_change` int(10) unsigned NOT NULL default '0',  `field1` varchar(255) collate utf8_unicode_ci default NULL,  `field2` varchar(255) collate utf8_unicode_ci default NULL,  `field3` varchar(255) collate utf8_unicode_ci default NULL,  `field4` varchar(255) collate utf8_unicode_ci default NULL,  `field5` varchar(255) collate utf8_unicode_ci default NULL,  `field6` varchar(255) collate utf8_unicode_ci default NULL,  `field7` varchar(255) collate utf8_unicode_ci default NULL,  `field8` varchar(255) collate utf8_unicode_ci default NULL,  `field9` varchar(255) collate utf8_unicode_ci default NULL,  `field10` varchar(255) collate utf8_unicode_ci default NULL,  `field11` varchar(255) collate utf8_unicode_ci default NULL,  `field12` varchar(255) collate utf8_unicode_ci default NULL,  `field13` varchar(255) collate utf8_unicode_ci default NULL,  `field14` varchar(255) collate utf8_unicode_ci default NULL,  `field15` varchar(255) collate utf8_unicode_ci default NULL,  `field16` varchar(255) collate utf8_unicode_ci default NULL,  `field17` varchar(255) collate utf8_unicode_ci default NULL,  `field18` varchar(255) collate utf8_unicode_ci default NULL,  `field19` varchar(255) collate utf8_unicode_ci default NULL,  `field20` varchar(255) collate utf8_unicode_ci default NULL,  `field21` varchar(255) collate utf8_unicode_ci default NULL,  `field22` varchar(255) collate utf8_unicode_ci default NULL,  `field23` varchar(255) collate utf8_unicode_ci default NULL,  `field24` varchar(255) collate utf8_unicode_ci default NULL,  `field25` varchar(255) collate utf8_unicode_ci default NULL,  `field26` varchar(255) collate utf8_unicode_ci default NULL,  `field27` varchar(255) collate utf8_unicode_ci default NULL,  `field28` varchar(255) collate utf8_unicode_ci default NULL,  `field29` varchar(255) collate utf8_unicode_ci default NULL,  `field30` varchar(255) collate utf8_unicode_ci default NULL,  PRIMARY KEY  (`id`),  KEY `username` (`username`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_c;

We've got all the "standard" user fields, plus the extra 30. So, we need another table to list the names of the various fields, whether they're even used, etc. That one looks like this:

CREATE TABLE `user_fields` (  `id` varchar(7) collate utf8_unicode_ci NOT NULL,  `shown` tinyint(1) unsigned NOT NULL default '1',  `disp_order` int(11) unsigned NOT NULL,  `description` varchar(255) collate utf8_unicode_ci default NULL,  `dropdown` tinyint(1) unsigned NOT NULL,  `allow_blank` tinyint(1) unsigned NOT NULL default '1',  `check_num` tinyint(1) unsigned NOT NULL,  `check_date` tinyint(1) unsigned NOT NULL,  `maxlen` int(11) unsigned NOT NULL,  `minlen` int(11) unsigned NOT NULL,  `maxval` varchar(255) collate utf8_unicode_ci NOT NULL,  `minval` varchar(255) collate utf8_unicode_ci NOT NULL,  `searchable` tinyint(1) unsigned NOT NULL default '0',  `editable` tinyint(1) unsigned NOT NULL default '1',  PRIMARY KEY  (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The id field there is 'field1', 'field2', etc, to match the users table. There are only ever 30 records in that table, nothing gets inserted or deleted. If the field is a dropdown, we have a third table to list the options for the dropdown.

CREATE TABLE `user_drop_options` (  `id` int(11) unsigned NOT NULL auto_increment,  `fid` varchar(7) collate utf8_unicode_ci NOT NULL,  `disp_order` int(11) unsigned NOT NULL,  `label` varchar(255) collate utf8_unicode_ci NOT NULL,  `val` varchar(255) collate utf8_unicode_ci NOT NULL,  PRIMARY KEY  (`id`),  KEY `fid` (`fid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Anyway, if you can live with a limit on the number of possible fields, that might be another option.

[boen_robot]

I'll consider it for other kinds of applications, but for this one, I don't think this can work out, as such things are often too "dynamic", "specific" and arbitrary large. When it's something for the user, as in your case, it's acceptable, as not everyone wants to enter everything, and even if they do, they have their "border of comfort" (and I think in your case, you're way beyond the normal person's threshold, so that's good).