To comma-separate or not

Report · May 05, 2006

I have to re-build an employee database (now counting 700 employees, in order to make it relational. The existing structure of the Employee - Skill table is like this:

EmployeeID | SkillID1| SkillID 2 |... counting to 15 skills.

When planning for the new table structure, I was planning on building a standard link table like this:

EmployeeID | SkillID

Then I read about comma-separated data, and I wonder if this is the way to go. For total simplification, I could put the whole shebang into the main Employee table along with all the other info about each employee. Like this:

EmployeeID (PK) | 16

EmployeeName | Jones

EmployeePhone | 555 123

SkillID (FK) | 1,3,4,6

SertificateID (FK) | 1,7

I know I would lose some flexibility in positioning the data output compared to a link table solution, are there other pro / cons about PHP/ MySQL comma-separated data input and display, especially putting everything into one single table??

Best regards, Erik

Report · May 05, 2006

"Tor Erik" <webforumsuser@macromedia.com> wrote in message
news:e3fvd0$1ie$1@forums.macromedia.com...

> Then I read about comma-separated data, and I wonder if this is the way to
> go.

NO!!!!

Why are you using a database if the first thing you're going to do is
violate the first normal form?
It's a dark path. Ignore it.

Report · May 05, 2006

>I have to re-build an employee database (now counting 700 employees, in
>order
> to make it relational. The existing structure of the Employee - Skill
> table is
> like this:
>
> EmployeeID | SkillID1| SkillID 2 |... counting to 15 skills.

Heh...I can sympathize. The number of Access DBs laying around our office
with the most arcane structures is staggering.

Every time I encounter one I finally determine it makes more sense to start
over than to even begin to understand the logic that went into it.

> I know I would lose some flexibility in positioning the data output
> compared
> to a link table solution, are there other pro / cons about PHP/ MySQL
> comma-separated data input and display, especially putting everything into
> one
> single table??

The big problem is updating. If you have a link table, and one skill is
deleted, it's a rather trivial task to delete it from the link table.

On the other hand, deleting it from any number of comma-delimted strings
would take quite a bit of string manipulation and overhead.

-Darrel

Report · May 05, 2006

quote:

Originally posted by: Newsgroup User

Every time I encounter one I finally determine it makes more sense to start
over than to even begin to understand the logic that went into it.

Right there with you.....

quote:

On the other hand, deleting it from any number of comma-delimted strings
would take quite a bit of string manipulation and overhead.

Point taken, Darrel. I'm doing the dirty job right now, connecting each employee to skills, certificates and so on, one by one in new link tables.

THX

-Erik-