Multiple values in a single mysql field

Question

I'm building a mysql database to store companies information. In one of the fields i need to store multiple values like the name of the founders.

I'm aware that won't be a good choice but it would avoid to create another table just to store a few names.

I'm concerning to the question regarding retrieving and querying the values on that specific field.

I really appreciate your input so that i can make a good choice even that require more code lines.

TIA

Hmmm, you know the right answer but are reluctant to do it the right way. Why should anyone attempt to answer? — Gordon Linoff, Jun 03 '15 at 14:28
create the other table. You'll find additional uses and benefits when your data is normalized in an appropriate way. — rwhite, Jun 03 '15 at 14:29
I know that the right answer is normalization, but i also know that there's exceptions that sometimes are more efficient. Why create a second table just for some names and other info if you can store it in a single field ? There's no problem in listenning to other's experience... — Joao Ferreira, Jun 03 '15 at 14:33
I recommend Bill Karwin's book [**SQL Antipatterns** http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557](http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557) Storing multiple values in a column is Chapter 2 in the book. (As of right now, you can read Chapter 2 in the Amazon "Look inside" preview.) — spencer7593, Jun 03 '15 at 14:36
Possible duplicate of [http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — spencer7593, Jun 03 '15 at 14:47

score 0 · Answer 1 · answered Jun 03 '15 at 14:32

0

Three tables: Companies, (General info Companies) CompanyFounders, (Founders connected to Companies) Founders. (Name of founders)

Get used to writing lots of code if you want to do stuff the proper way.

answered Jun 03 '15 at 14:32

solid_luffy

361
2
15

score 0 · Answer 2 · answered Jun 03 '15 at 14:33

Well, just do it the right way. It is not that much work to create the table, if you are willing to write more lines of code for the worse solution, why not just invest the energy in the good one.

Is there a reason why you do not want to normalise your database in this regard?

bish · Accepted Answer · 2015-06-03T14:42:31.860

As you are aware of the problem when storing multiple values in one col you should not do it. Keep your database normalized and you will have much less problems retrieving and working with your data.

Further information: http://en.wikipedia.org/wiki/Database_normalization

For your example you should use three tables companies, persons, founder, while founder links the primary keys of companies and persons together.

edit: SQLfiddle seems to have some problems at the moment so I can't provide you one, but heres an example-code: com1 has one founder, com2 has three.

create table companies (id, name);

insert into companies (id, name) values (1, 'com1');
insert into companies (id, name) values (2, 'com2');

create table persons (id, name);
insert into persons (id, name) values (1, 'person1');
insert into persons (id, name) values (2, 'person2');
insert into persons (id, name) values (3, 'person3');
insert into persons (id, name) values (4, 'person4');

create table founders (company_id, person_id);

insert into founders (company_id, person_id) values (1,1);
insert into founders (company_id, person_id) values (2,2);
insert into founders (company_id, person_id) values (2,3);
insert into founders (company_id, person_id) values (2,4);

Multiple values in a single mysql field

3 Answers3