Correct way to set up MySQL tables

Question

I can't work out what I should be doing here...

I have a database with around 20,000 records. Each of these records has about 20 columns to it.

I want to add around 20 or so additional columns to this database which would be on the lines of a load of different URLs for each record. Mostly, these will be blank.

What's the "right" way of doing this:

1. Add 20 additional columns (youtubeurl, facebookurl, etc) (Benefits: only one URL call // Drawbacks: makes my database much larger)

2. Add an additional table with three columns - 'ID','URLType','URL' which I can additionally call? (Benefits: keeps main table much smaller // Drawbacks: additional SQL query required)

What should I be doing?

Answer 1

Option 2 is a almost certainly the better option. It makes it easier for you to add new Url types in the future (just invent a new URLType instead of having to create a new column). Pages that use these urls then don't have to be modified to accomodate the new type of URL; they'll just pick it out of the table. In other words, you only have to make a change in one place instead of several.

If people mostly have only a few of these urls, splitting it into a separate table is almost certainly the way to go.

Answer 2

Everything else being equal, I would go with option (2). This allows you to keep your data normalized and offers flexibility if you need to add more sites in the future.

FWIW, this does not require an extra query to SELECT data, as you can just JOIN to the other table. But of course, it would require extra INSERT / UPDATE queries.

Answer 3

Everything you're adding is a URL. Each URL is related to one (or maybe more) of your current records. So either:

for URLs that have only one record- urls table with url and FK to records table

or for URLS that can relate to more than one record- urls table with url_id and url linking table with record_id and url_id