garek007 garek007 - 3 months ago 13
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

USERS
and
PROJECTS
. I want to start storing company specific information and relate it to the
USERS
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:

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

USERS
Name | Email | Date Created | Password


Thanks in advance for any insights you can offer.

Answer

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.