-1

I'm trying to decide the best possible way to structure my user table. Users can have access to multiple "brands" and each brand will have multiple tables. That is, if user X wants to see data for brand Y, the database contains the information to say which tables I need to make calls to.

For example, user X can access Brand1 and Brand2. Brand1 has its data in table1, table2, and table3. Brand2 has its data in table4, table5, table6. User selects Brand2 and the application makes a call to find out that table4, table5, table6 should be used until user selects a different brand.

What's the best way to structure this knowing that a single brand might have multiple users that can access the data?

Do I need more than just a user table and, if so, what else and how would that connect to the user table?

Thanks.

jonmrich
  • 4,233
  • 5
  • 42
  • 94
  • 4
    A table for each brand.... stinks to the heavens of bad design.... one `user` table, one `brand` table and a `user_brand` table that links users to brands (defining which brands a user can see) – Mark Baker Feb 24 '15 at 20:17
  • I figured it would be multiple tables, but I'm stuck on what goes in each (i.e., what the columns are). If I make a brand table and I want to include which users have access, what are the columns especially where I need to indicate multiple users with access to that brand? – jonmrich Feb 24 '15 at 20:20
  • Don't overuse tables... read more about normalisation http://en.wikipedia.org/wiki/Database_normalization - http://holowczak.com/database-normalization/ – bakriawad Feb 24 '15 at 20:22
  • users get 1 table, brands 1 table, you can put some columns that you can define power and positions which you can use to manipulate what you need... the owner, admin,mod,member and guest are all stored in 1 table, but 1 column in that table is what defines who they are... – bakriawad Feb 24 '15 at 20:25
  • Sorry...still not clear to me. If I have a brands table, what is in that table (i.e., columns) that show the users that have access to it? I can't have all the users piled into one "cell". Same on the user side, what to indicate that the have access to multiple brands? Or do I have the same user with data in multiple rows (i.e., one row per table they have access to)? – jonmrich Feb 24 '15 at 20:31
  • 1
    You don't put anything in a `brand` table to say what `users` can access what `brands`.... you use a `user_brand` table for that, which has just two columns, `user_id` and `brand_id` – Mark Baker Feb 24 '15 at 20:34
  • @MarkBaker So, a given user can have multiple rows in the table, each with a unique brand_id? – jonmrich Feb 24 '15 at 20:35
  • Sorry to be "that guy" but this is off topic. This has more to do with databases and nothing at all to do with programming or anything specific to PHP. You'd be better off asking this in the database stack exchange. – leigero Feb 24 '15 at 20:37
  • Start by reading [this article](http://www.databaseprimer.com/pages/relationship_xtox/) about many-to-many relationships – Mark Baker Feb 24 '15 at 21:10

2 Answers2

1

Like Mark Baker pointed, you can have one user table, one brand table and one user_brand table.

user table - stores user_id (and other user data) brand table - stores brand_id (and other brand data)

You've already defined relationships between users and brands. It's M:N (many to many), which means that:

  • one user can have access to multiple brands.
  • one brand can be accessed by many users.

Table user_brand solves the access problem.

user_brand table - stores user_id and brand_id (and optional data which better describes this relationship).

Here is an example about sql syntax (enforcing foreign key constraints).

Community
  • 1
  • 1
Whirlwind
  • 14,286
  • 11
  • 68
  • 157
-2

You can use GRANT query so the user can access just 2 tables in a database, then in the application, you can code it just to select 1 table, until the user changes the brand. The brand itself is the table, isn't it?

In PHP code, the code and query should be like this:

<?php
$db = new mysqli('hostname', 'db_username', 'db_password', 'db_name');
$brand = $_SESSION['brandName']; // use this if you use sessions to cache the data
$db->query("SELECT * from `$brand`");
Andreas
  • 25
  • 3