Insert Multivalue, MYSQL DB Structure

Question

The Following Codes are examples I made up to depict my situation.

I have 3 html 'Selects' with multiple options. Each day, users have to select a minimum of one option for each 'Select'.

<html>
<head></head>
<body>
<form id="frm" action="Save.php" method="POST">
<input type="text" id="datepicker" name="datepicker" readonly='true'>
<select id="OptionType1" multiple="multiple">
<option value="1">Option 1</option>
<option value="2">Option 2</option>
<option value="3">Option 3</option>
</select>
<select id="OptionType2" multiple="multiple">
<option value="1">Option 1</option>
<option value="2">Option 2</option>
<option value="3">Option 3</option>
</select>
<select id="OptionType3" multiple="multiple">
<option value="1">Option 1</option>
<option value="2">Option 2</option>
<option value="3">Option 3</option>
</select>
</form>
</body></html>

CREATE TABLE `m` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Client ID` int(11) DEFAULT '0',
`Date` date DEFAULT NULL,
`Category` int(11) DEFAULT '0' COMMENT 'categoryA-1,categoryB-2,categoryC-3',
`OptionsType1` int(11) DEFAULT '0',
`OptionsType2` int(11) DEFAULT '0',
`OptionsType3` int(11) DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This DB Structure can only take one OptionType value each.

Client ID,Date,Category are used to make a unique index. The unique index is used for "Insert Into... ON Duplicate Key Update". The reason I used the unique index is so the user can go back and edit their option and mysql will update it based on the unique index.

Due to the Unique Index I cannot make multiple rows with the same Client ID,Date,Category. My only other option is to concatenate my values together in the same row but that could cause performance issues if I decided to look for those values down the line.

Can anyone help me with this dilemma. I need the right structure to save my multiple values and be able to load it back on html and have the user able to update it. Thanks

UPDATE: Two tables are created

CREATE TABLE `m` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Client ID` int(11) DEFAULT '0',
`Date` date DEFAULT NULL,
`Category` int(11) DEFAULT '0' COMMENT 'categoryA-1,categoryB-2,categoryC-3',
PRIMARY KEY (`ID`),
UNIQUE KEY `uniq` (`Client ID`,`Date`,`Category`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `c` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`OptionsType1` int(11) DEFAULT '0',
`OptionsType2` int(11) DEFAULT '0',
`OptionsType3` int(11) DEFAULT '0',
`REF ID` int(11) DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Is there a way to replace existing data (c table) without deleting. Unique index does not exist on c table only primary key but i do not keep track of that when loading html form.

date column is there, can you rephrase your question i'm not understanding it. Thanks — Ninjatalon
– Ninjatalon, Commented Jun 26, 2018 at 21:31
I misread. Don't have the OptionType columns. Use 2 tables and have OptionType and the value in the other table with an id that maps back to the m table. — user3783243
– user3783243, Commented Jun 26, 2018 at 21:39
Separate m table with ID,Client ID,Date,Category and c table with Primarykey ID,OptionsType1,OptionsType2,OptionsType3,Reference ID and join m.IDon c.Reference ID? Seems like a good idea — Ninjatalon
– Ninjatalon, Commented Jun 26, 2018 at 21:46
I think that will solve my DB issue. but I think that might cause me headaches with my "Insert Into on duplicate key update" — Ninjatalon
– Ninjatalon, Commented Jun 26, 2018 at 21:50
You don't need the insert/update query at that point. If it is an insert that conflicts it is an error no because they should't be able to update multiple times? If I misread though than issue the constraint and update on the second table. — user3783243
– user3783243, Commented Jun 27, 2018 at 4:17

arueckauer · Accepted Answer · 2018-06-27 18:23:30Z

1

If you don't want to concatenate, you can create a new table just for the values and referencing the m table.

For the m table you can replace the OptionsType[1-3] fields by one column wherein the number of the OptionsType is persisted and adding this new column to the shared unique key.

In the values table, you reference the id of the m table and place therein the option number and the user selection.

To elaborate on the solution, after a referencing table got accepted and the question was adjusted:

First I show the table creation, afterwards I explain it.

Table m

CREATE TABLE `m` ( `ID` INT(11) NOT NULL AUTO_INCREMENT, `Client ID` INT(11) NOT NULL, `Date` DATE NOT NULL, `Category` INT(11) NOT NULL COMMENT 'categoryA-1,categoryB-2,categoryC-3', `OptionsType` INT(11) NOT NULL, PRIMARY KEY (`ID`), UNIQUE INDEX `Client ID_Date_Category_OptionsType` (`Client ID`, `Date`, `Category`, `OptionsType`) ) ENGINE=InnoDB;

Table c

CREATE TABLE `c` ( `ID` INT(11) NOT NULL AUTO_INCREMENT, `mID` INT(11) NOT NULL, `Option` INT(11) NOT NULL, `Value` INT(11) NOT NULL, PRIMARY KEY (`ID`), UNIQUE INDEX `mID_Option` (`mID`, `Option`), INDEX `mID` (`mID`) ) ENGINE=InnoDB;

Explanation

Add OptionsType to table m. In table c you reference table m via m.ID=c.mID. To ensure no duplicates are being persisted, a unique index is set over mID and Option.The Option column shows which option is referred to and value shows the selection of the user. With this schema you're also not limited to three options per select.

Example query

SELECT * FROM `m` INNER JOIN `c` ON `m`.`ID`=`c`.`mID`; +----+-----------+------------+----------+-------------+----+-----+--------+-------+ | ID | Client ID | Date | Category | OptionsType | ID | mID | Option | Value | +----+-----------+------------+----------+-------------+----+-----+--------+-------+ | 1 | 4711 | 2018-06-27 | 1 | 1 | 1 | 1 | 1 | 0 | | 1 | 4711 | 2018-06-27 | 1 | 1 | 2 | 1 | 2 | 1 | | 1 | 4711 | 2018-06-27 | 1 | 1 | 3 | 1 | 3 | 0 | | 3 | 4711 | 2018-06-27 | 1 | 2 | 7 | 3 | 1 | 0 | | 3 | 4711 | 2018-06-27 | 1 | 2 | 8 | 3 | 2 | 1 | | 3 | 4711 | 2018-06-27 | 1 | 2 | 9 | 3 | 3 | 0 | +----+-----------+------------+----------+-------------+----+-----+--------+-------+ 6 rows in set (0.00 sec)

edited Jun 27, 2018 at 18:23

answered Jun 26, 2018 at 21:34

arueckauer

3525 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ninjatalon Over a year ago

Thanks user3783243 also mentioned that which sounds about the right structure for my DB but it might cause issues with my insert update query

arueckauer Over a year ago

I edited my answer to go a bit more into detail. This should make the schema question clear. As for the insert and update queries, you probably want to check first if an entry already exists. But this is another question.

Ninjatalon Over a year ago

Thanks for your update, I really appreciate you taking the time to help me with this. Can you explain what the option column is for? If option column has "1" that cannot be used to determine that the first option was selected because the order of options can change. Value column is used to save the option's value and ill use that to load the saved data back to the form. So is it necessary to have UNIQUE INDEX mID_Option when i can have multiple values linking to UNIQUE INDEX Client ID_Date_Category_OptionsType

Ninjatalon Over a year ago

I believe I have figured out insert, update and delete data. I will post my method once i complete it. i will mostly use the schema you provided thanks

arueckauer Over a year ago

I see the confusion. As I look at it now, the naming can be improved. To explain: option is meant for the HTML option value and value for whether the user has selected the option or not.

|

Collectives™ on Stack Overflow

Insert Multivalue, MYSQL DB Structure

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related