-2

I want to create a download center in my website where specific users can download specific content for which they have paid for. I thinking of creating a MySQL table like :

items | user1 | user2 |user3 
----------
item1 | true  |false  |false
----------
item2 | false | true  |false
----------

and then checking if user is allowed. In this case I have to add a new column for every new user and this could be a problem with increasing no. of users.

I doubt if this is a good or efficient way.

Please suggest if there is any better approach.

Pang
  • 9,564
  • 146
  • 81
  • 122
Pushkar Singh Rawal
  • 3
  • 2
  • 2
    Yes, that is a problem: http://www.amazon.ca/Database-Design-Mere-Mortals-Relational/dp/0201752840 or many others can help you in this. – Norbert Sep 19 '15 at 03:10
  • Better if you have an items table and a permissions table that links item ids to users. Items (itemID,itemName) and permissions (itemID,userID,hasPermission). You should definitely look around on google for help with database design –  Sep 19 '15 at 03:14
  • remove the 'add a column' as an option – Drew Sep 19 '15 at 03:20
  • @ Terminus I got it. Thankyou – Pushkar Singh Rawal Sep 19 '15 at 06:13

1 Answers1

0

Actually you are right. Having such intended increasing number of columns is a sign of poor relational design. This design is called "One Big Spreadsheet", that is generated when you want to treat relational database as an MS-Excel file.

The solution for that is to create 3 tables (below demonstration is the minimum columns you will need):

  1. users table with columns: id, username
  2. files table with columns: id, code
  3. users_files table that connects the other two, with columns: id, user_id (FK), table_id (FK)

Where (FK) stands for Foreign Key This is a many to many relation.

Musa Haidari
  • 2,109
  • 5
  • 30
  • 53