0

I have one table that I want to separate them into two to normalize the data. I want a opinion of the best way of doing it.

My table has lots of columns and one of them I was storing Varchar values separated by comma, for instance: John, Mary, Joseph and so on. Now I created a new table called names. This table has two columns: name_id (primary key), name (the same varchar).

My doubt is because I ended up with the same thing, but now, instead of saving the names, I'm saving the primary keys. For instance: 1,3,5.

Is there a better way of saving this data to better organize those tables?

2
Contributors
3
Replies
18
Views
3 Years
Discussion Span
Last Post by deceptikon
1

When you're storing a list of entities in a column, that suggests you have shared data which can be normalized. Let's take your names example and make up something to show the setup.

Denormalized

business_unit:
    bu_id, managers, address

-- Separating the manager names is a chore
select managers from business_unit where bu_id = @bu_id;

Normalized

business_unit:
    bu_id, address

employee:
    emp_id, bu_id, name, is_manager

-- Names are separated naturally
select name from employee where bu_id = @bu_id and is_manager;

One obvious benefit of this is the managers can be abstracted into employees and share the same table.

0

Here's a better way to understand:

Click Here

There's a column with lots os questions marks (???), I want to know how I can save the data from the other table in this column. A way that is gonna be easier to access later.

Edited by foreba: grammar

1

Consider a linking table. Remove tecnico from agendamento_diario then add a new table for linking the two together:

agenda_link:
    agenda_id, tecnico_id
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.