Is it okay to dynamically create a MySQL table?

Question

I'm building a aweber-like list management system (for phone numbers, not emails).

There are campaigns. A phone number is associated with each campaign. Users can text to a number after which they will be subscribed.

I'm building "Create a New Campaign" page.

1. My current strategy is to create a separate table for each campaign (campaign_1,campaign_2,...,campaign_n) and store the subscriber data in it.

2. It's also possible to just create a single table and add a campaign_id column to it.

Each campaign is supposed to have 5k to 25k users.

Which is a better option? #1 or #2?

Answer 1

Option 2 makes more sense and is widely used approach.

Answer 2

I suppose it really depends on the amount of campaigns you're going to have. Let's give you some pros/cons:

Pros for campaign_n:

• Faster queries
• You can have each instance run with its own code and own database

Cons for campaign_n:

• Database modifications are harder (you need to sync all tables)
• You get a lot of tables

Personally I'd go for option 2 (campaign_id field), unless you have a really good reason not to.