How to normalize mysql database table

Question

For a mysql database I have two similar tables:

1.st table track_tag (2 million rows)

id (int), track_id (int), tag (varchar)

2.nd table tag (150.000 rows)

id (int), tag(varchar)

I want to normalize first table tag column with the second table's primary key. What is the fastest way to solve this problem?

Does each track_id correspond to a unique tag ? If so, then just remove the tag column from track_tag. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 4, 2016 at 5:01
Fastest way in terms of what? Design? Two seconds. Actually implementing and moving data? Longer. — domdomcodecode
– domdomcodecode, Commented Feb 4, 2016 at 5:01

Tersosauros · Accepted Answer · 2016-02-04 05:07:04Z

1

Assuming you're not working on a production DB (and hence things like long-running queries, and dropping/re-creating tables are OK) - then I think the fastest way would be to use a Create Table AS SELECT statement.

For example:

CREATE TABLE track_tag_V2 AS SELECT track.id AS id, track.track_id, tag.id AS tag_id
FROM track_tag track, tag
WHERE track.tag = tag.tag
;

And then (Assuming the structure is to your liking) simply DROP track_tag and then RENAME track_tag_V2 to track_tag and you're done!

However, this may not (and probably will not) the the BEST way. If your DB is de-normalised to this point, it may have been done for performance reasons already (normalisation optimises storage, not performance). It may also be in need of a total re-design (which is not fast).

answered Feb 4, 2016 at 5:07

Tersosauros

9231 gold badge12 silver badges22 bronze badges

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

Comments

Zafar Malik · Accepted Answer · 2016-02-04 05:18:51Z

0

Your tag table is fine with columns as : id (int), tag(varchar)

Note: Assuming id field is here primary key other wise create index on it.

But change track_tag table as per below-

id (int), track_id (int), tag_id (int)

Note: tag_id field should be indexed here.

Also not clear what is track_id field here.

answered Feb 4, 2016 at 5:18

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

Collectives™ on Stack Overflow

How to normalize mysql database table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related