What is the best approach to my PHP / MySQL problem?

[milesap]

So I'm developing a website for a few local car dealerships (10 dealerships) where people can signup, book service appointments, see the status of repairs, and where the dealership can login and view this information on my website. One requirement placed on me was that if someone signs up for car dealer A that none of the other car dealerships could see that user, their service appointments and so on (for obvious reasons). What I would like to know is how to best approach this situation. I believe I have two options:

 

1) On every table in MySQL put a car dealer id so you know which dealership they belong to. Like under the 'user' table you put a 'dealerId' field to differentiate who belongs where. This solution sounds best at first until you start to have 10+ tables where all the info needs to be sorted by dealership. This may also be a slower option for PHP and MySQL as it must sort through all the rows and find the one's for a particular dealership.

 

2) Create a new database for each dealership. This seems easier to implement and requires less server processing. However if I have 10+ databases, how easy is it to modify, export, or add fields to all 10+ databases. If I have to add say 'username' to the database, would I now have to manually update all 10?

 

Any advise on this issue, or suggestions would be great (even if it's something I didn't mention here).

[abazoskib]

you would have a user table with purely user info(user_id as primary key)

 

you would have a dealership table with only dealership data(dealership_id as primary key)

 

appointment table would have a user_id and a dealership_id column

 

bare bones example, but if you need more detail let me know