Adding New Columns to Existing or Auxiliary Table in MySQL

MySQLoptimization

I have a database with the following table:

stores
- ID
- name
- phone number
- email address

After adding around 4000 stores to the database we figured that we will need to allow store owners to create a more complete profile. This means we will have the following optional fields:

address, zip code, description  and website URL

My question: should I add these new columns on the existing table or create a new one called store_details, with a foreign key to main table?

I am using MySQL, and each store can only have 1 of those fields.

Consider that only 30% of store owners are going to use them by creating a complete profile.

I am mostly worried with performance when the database grows to say 500,000 entries. For example, when fetching all stores to perform a keyword search. One could argue that by using an axillary table I could save some memory both on disk and on RAM when manipulating the data, but not not sure if this is significant or not.

Best Answer

You haven't specified a database platform, but my experience with MS SQL server is that you should put those fields in the same table, unless you need them separate for other reasons (like if a single store can have multiple addresses).

Any performance concerns you have can likely be resolved with proper indexing, based on how you believe the table will be queried.

Related Solutions

MySQL Database Design Help

admin and active is just a bunch of 0's and 1's so can I do something like this? Since there will be 99% active and only a couple that will be inactive.

admin - id, user_id
inactive - id, user_id
social - id, user_id
facebook_link, twitter_link
member_forgot - id, user_id, forgot_code

Actually no, I wouldn't do this and keep it in the same table. The flag should be enough to distinguish between these two. The only difference in your queries is a active = true. Personally, I wouldn't consider this a bad design choice (altough your idea is not wrong, I would not use it in this case with - I'm guessing - a small set of data). Since only on set of data belongs to a single user, JOINs would make not that much sense.

I really want to practice splitting it up in different tables even if it is not a huge database, or is that a bad idea? So if I delete a user from the users table, it will delete all of the other records in the other tables associated with that user_id?

Again, I wouldn't do that personally(!). I don't see a benefit of seperating user centric data from the user. If you fear losing the data on deletion, introduce a deleted flag. This way, you will never lose data and your application just respects the deleted flag in your queries (this would also apply if you split the data accross tables).

Credit_Cards = 1110 format (visa, mastercard, discover, amex)

Storing credit cards is always very sensitive, according to PCI (as far as i remember) you are not allowed to store the CVV code (hope someone can correct me on that). But since this seems to be a fictional application, that should not be a problem.

Mysql – Trigger to delete inserted record if it doesn’t exist in another table

Well, it's a crazy and novel idea, but you might consider that this is what foreign key constraints are actually for... you can't insert a row in table "x" if a column there has a foreign key constraint against a column in table "y" but the value you're inserting in "x" doesn't exist in "y"...

mysql> create table t1 (
         id int not null auto_increment primary key
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.40 sec)

mysql> create table t2 (
         id int not null auto_increment primary key, 
         t1_id INT NOT NULL, 
         FOREIGN KEY(t1_id) REFERENCES t1(id)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.49 sec)

mysql> insert into t2 (t1_id) VALUES (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))

mysql>

Best Answer

Related Solutions

MySQL Database Design Help

Mysql – Trigger to delete inserted record if it doesn’t exist in another table

Related Question