1

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.

  • 1
    What database platform are you using? You might get different answers. I assume a single store will never need multiple addresses? – BradC Aug 6 at 16:57
  • @BradC MySQL and correct. – Daniel Scocco Aug 6 at 17:05
  • Keyword search -- This can be done in efficient ways or in terribly inefficient ways. Think about it now; the JOIN could make a difference. – Rick James Aug 6 at 18:54
1

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.

  • That is what I suspected. Thanks! – Daniel Scocco Aug 6 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.