garek007 garek007 - 2 months ago 4
MySQL Question

Recommendations for table structure in MySQL

Hi I've got a small internal project I am working on. Currently it only serves my company, but I'd like to scale it so that it could serve multiple companies. The tables I have at the moment are

. I want to start storing company specific information and relate it to the
table. For each user, I will have a new column that is the company they belong to.

Now I also need to store that companies templates in the database. The templates are stored as strings like this:

"divider","events","freeform" etc.

Initially I was thinking each word should go in as a separate row, but as I write this I'm thinking perhaps I should store all templates in one entry separated by commas (as written above).

Bottom line, I'm new to database design and I have no idea how to best set this up. How many tables, what columns etc. For right now, my table structure looks like this:

Project Number | Title | exacttarget_id | Author | Body | Date

Name | Email | Date Created | Password

Thanks in advance for any insights you can offer.


What I would do is create 2 tables:

I would create one table for the different companies, lets call it COMPANY:

Company_id | Title | Logo | (Whatever other data you want)

I would also create one table for the settings listed above, lets call it COMPANY_SETTINGS:

Company_id | Key | Value

This gives you the flexibility in the future to add additional settings without compromising your existing code. A simple query gets all the settings, regardless of how many your current version uses.

SELECT Key, Value FROM COMPANY_SETTINGS WHERE Company_id = :companyId

Te results can then be put into an associative array for easy use throughout the project.