0

Now I have two way to do this as below, but I do not know which is better and is there some better way?

CREATE TABLE `params_value1` (  
  `params_id` int(11) NOT NULL AUTO_INCREMENT,  
  `params_value_int8` tinyint(4) DEFAULT NULL,  
  `params_value_int16` smallint(1) DEFAULT NULL,  
  `params_value_int32` int(11) DEFAULT NULL,  
  `params_value_string` varchar(32) DEFAULT NULL,  
  PRIMARY KEY (`params_id`),  
  UNIQUE KEY `params_id_UNIQUE` (`params_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  

OR

CREATE TABLE `params_value2` (  
  `params_id` int(11) NOT NULL AUTO_INCREMENT,  
  `params_value` varchar(32) NOT NULL,  
  PRIMARY KEY (`params_id`),  
  UNIQUE KEY `params_id_UNIQUE` (`params_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  

The follow picture is main tab used:

enter image description here

  • 1
    How do you intend to use this information? If you have all of them together (_int8, _int16, ...), how do you know which one contains de intended value? – joanolo Jan 11 '17 at 8:24
  • Yes,this is only parts tab of my project, and I have add the main tab used in my project . – Levy He Jan 12 '17 at 6:52
2

Entity-Attribute-Value is the nightmare you are embarking on. See the additional tag I added. See a similar tag in stackoverflow.com; there is a lot more discussion there.

It is traditional to have another column in the Attribute table: entity_id, attribute, value. Your question applies to how to store value; that is only part of the nightmare.

In the cases of integers, simply make it BIGINT; that will hold the various sizes (with a little wasted space), and simplify your code some.

  • I just know that this is an EAV problem ,thank you very much – Levy He Jan 12 '17 at 7:14
1

Best way to store dynamic parameter key/values are as JSON.

0

If I understood correctly, this can be done by using following method:

enter image description here

create table param_type
(
paramtype_id int,
paramtype varchar(30),
paramunit varchar(30),
prmary key(paramtype_id)
);

create table param_value
(
param_id int,
paramtype_id int,
paramvalue varchar(30),
parimary key(param_id),
foreign key(paramtype_id) references param_type(paramtype_id)
);

There are other multiple ways to implements this, still it depends on your exact requirement. I hope this will help you!!

  • Yes my tab mode is like this , thank you very much! – Levy He Jan 12 '17 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.