Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2009
    Location
    another horrible american
    Posts
    7

    Online service/subscription

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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)"
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2009
    Location
    another horrible american
    Posts
    7
    A userID would be related one to many ProductsOwned.

  4. #4
    Join Date
    Feb 2009
    Location
    another horrible american
    Posts
    7
    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.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by ecsmoore
    I am looking for a table structure that allows me to lookup whether this user has access to (paid for) specific online products
    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.

  6. #6
    Join Date
    Feb 2009
    Location
    another horrible american
    Posts
    7
    Thanks Mike... Would you mind giving me a 2 cent version of why the primary key structure?

  7. #7
    Join Date
    Feb 2009
    Location
    another horrible american
    Posts
    7
    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?
    Last edited by ecsmoore; 02-05-09 at 09:48.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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...
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by ecsmoore
    Thanks Mike... Would you mind giving me a 2 cent version of why the primary key structure?
    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 )
    )

    Quote Originally Posted by ecsmoore
    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?
    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.

  10. #10
    Join Date
    Feb 2009
    Location
    another horrible american
    Posts
    7
    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

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    EDIT - Nevermind - should take my own advice
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2009
    Location
    another horrible american
    Posts
    7
    I wonder what that means.... pootle flump

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by ecsmoore
    I wonder what that means.... pootle flump
    probably the TSTH
    which could be...
    Terribly Sophisticated Talented Helper

    but it isn't.....
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ecsmoore
    I wonder what that means.... pootle flump
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •