MySQL数据库中的父子结构或序列化数组？

Question

I am trying to make a good decision about how to structure my MySQL database.

I can best explain this by example:

I have a table called customers. A customer can control one or more sites. Sites has his own table. The tables customers and sites have a primary key id (AUTO-INCREMENT). Most customers can modify multiple "sites" and we are growing towards 100.000 customers. All I need to do with this information is to check if a customer can edit a site.

The question is: how can I store my data best if I want to keep the queries to a minimum and the performance best? 1. I create a serialized array of all "sites" that a customer can edit and put it in a column in customers table. 2. I create a child-table for customers called customer-site in which I write all site_id's that the customer has access to.

As you probably can read I don't have a lot of knowledge, so any information why is very appreciated!

Best regards, Abayob

Answer 1

You should create a permissions table which will hold two columns short_name, name. You should create another table which will hold the customer permissions to sites customer_permission_site with customer_id, site_id, permission_short_name. Then check with the short name field from your code if the user has permissions.

Your short_name field in permissions table will be primary key for the table.

Benefits for this architecture

1. You can easily set an user to a site for edit, delete etc permissions.
2. You can set permissions to be given a user to a site in permissions table
3. Normalized & easy to search & retrieve.