I am wondering if anyone has experience with how to manage permissions for individual users that purchase access to online services, like a resource library. I am looking for a table structure that allows me to lookup whether this user has access to (paid for) specific online products. I also have to display to the user what products are owned and expiration date, etc. I have some ideas but am looking for someone that has maybe done this before. This system is built on MS SQL Membership System and has a custom related user table. I was thinking I would add a permissions table. Not sure how to structure this. One row for each product that the user has permission for? Or one row with a column for each product and then mark it as true if they own it? Anybody have ideas? Thank you. Michael Moore - St Paul MN USA

Let's say you have 100 products and I am a new user to your site. Are you going to create me 100 rows in your permissions table when I sign up? Does this not seem like a lot of redundancy?

Identify your entites and how they relate: users are an entity, products are an entity... "one/many user(s) can have/own/etc one/many product(s)"

A userID would be related one to many ProductsOwned.

I was thinking more along the lines of adding one row for each product they purchased. Which would in most cases, due to the type of services, be less than 4 each year.

I have no experience doing this but it doesn't seem difficult. You have a table of users, a table of available products and you have a relationship table in the middle detailing what products each user owns. It should have one record for each product that a user owns.

Code:

create table UserProducts (
   user_id           int,
   product_id        int,
   expiration_date   datetime,
   primary key ( user_id, product_id )
)

If you have separate columns for each product then you'll have to alter the table each time you want to add a new product. It would also be more difficult managing the expiration dates for each product if you have multiple products on one row.

Thanks Mike... Would you mind giving me a 2 cent version of why the primary key structure?

Maybe the primary key is for speed if the table gets large. But, would you still do a Select, such as, Select Count(user_id) where product_id = 424 and user_id = 10045 and expiration_date > '2-6-2009'? Or does this primary key allow a select with less verbage?

I think you would have to go down the one row per member per product.. as so called intersection table

why?
unless you know you will NEVER add products you will have to go back in and tinker with the design and the application each and every time a product gets changed

ultimately its a more flexible solution
get a new product, add it to your product table, the existing software runs with no code changes (wither SQL or application)
you coudl store more than just whether they have paid... it could also store if or the number fo times they have looked at the product, or no times downloaded it
you could develop a more proactive sellign approach similar to Amazons
well others have bought blah
or we know you'v elooked at blah
<shamelessplug>or we know you are interested in SQL have you read Rudy's Simply SQL yest</shamelessplug>

you could then also store when they looked or bought the product if its an on-line product you could record how long someone is allowed to download it.
you could build on that by offering discounts on what people have bought or based n the numbe rof products that person ahs bought within the last n periods... that allow syou to send out a chase email
fred smith your discount code runs out in the next 30 days, click here if you wish...

If all you were doing is storing what products a user has then there could only be one row for a given user_id / product_id combination. If you want to find all the products a user has then this index will speed the query up. Similarly if you wanted to find out whether a user has a particular product. It won't be much use if you wanted to find all the users who have product x though.

If you want to go further and add healdem's ideas (which all sound good) then you'd want to add a type field (indicating whether the user owns the product, has just looked at it or has had the product suggested to them because others have it) and a count to say how often they have looked at it etc. The primary key would then have to include the new type field. Something like

Code:

create table UserProducts (
   user_id           int,
   product_id        int,
   relation_type   varchar(5), ?
   relation_count  int,
   expiration_date   datetime,
   primary key ( user_id, product_id, relation_type )
)

The primary key is there mainly to ensure uniqueness but it also provides and index to speed up queries. The query above would run quickly but I'm afraid you'd still have to type it all out.

Thanks to you all.
healdem, those are good ideas and the shameless reference cracked me up.
Mike, thanks for explaining the key deal.
It is all good. Thanks

EDIT - Nevermind - should take my own advice

pootle flump ur codings are working excelent.

I wonder what that means.... pootle flump

probably the TSTH
which could be...
Terribly Sophisticated Talented Helper

but it isn't.....

It means I wrote something telling Mike he should read your post better, then reread your post, realised I was being a numpty and so edited my post so that no one will ever know.

pootle flump ur codings are working excelent.