Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,441
    Thanks
    274
    Thanked 32 Times in 31 Posts

    structure question

    I will do my best to try to explain here what im trying to do.

    I want to do a new table that consists of items purchased but its not your basic purchase table.

    This table will be used in an environment which it costs you credits to do stuff. Lets take this site for instance codingforums. Lets say for me to post this question it costs me 10 credits and each credit lets say is worth a penny so it costs me a dime to post this.

    I not only need to record that i purchased for credit deduction i also need to record this for history sake because now that i have paid the credit, any replys to this post i can answer for free.

    So you kinda where im going with this, to record that i paid for a feature and there will be quite a few (sending email, looking at vids etc)

    So i am sitting here thinking about table structure.

    A. I could have a multi field table that lists all the features as fields and then just make one row each time a purchase is made and then find that row if i need it. But that could end up being alot of rows.

    B. or i could have one table per feature and then list who bought that feature by having the table field as a stored array listing every member that purchased that feature then all i have to do is pull the row, do a quick search of the array to see if the member id is in there and thats it.

    The problem i am having is that there are pros and cons to each choice, and i have not decided which way to go yet. Usually what happens many times is that (sort of like a painting) once i get to working on it, it decides which way to go for me and takes on its own life sort of.. lol

    Any suggestions.. Thanks.

    Thought i would add alittle layout here of choice A and B above.

    A. Feature table
    field names Feature1 feature2 feature2 and so on
    for every purchase a new row with one of those featuresfields having new data (so basically the rest of the row is wasted)

    b.
    Feature 1 table
    Row whopurchased (field which contains an array of userid's)

    Feature 2 table
    Row whopurchased (field which contains an array of userid's)

    Feature 3 table
    Row whopurchased (field which contains an array of userid's)


    Maybe that will make alittle more sense now.
    Last edited by durangod; 11-26-2012 at 03:06 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I think you've already answered at least a good chunk of your own question:

    Quote Originally Posted by durangod View Post
    Row whopurchased (field which contains an array of userid's)
    Never create a collection in a single field. Ever. So that means option b is out.
    What you will have is a many to many (many featured can be used by many users). So what you will need for sures is the users, the feature, and the userfeature table (where the userfeature can be named whatever to indicate that it is purchased), so that's a minimum of three tables in order to flatten a many to many.

    How you apply it though will be mostly programatically. With an example as you have posted, that would indicate that replying is always "free", but creating a topic would cost. So you would deal with that at the topic level, but not the reply level. If a post is always paid for, except where you have already payed for a reply would be done by simply collecting everything in topic, and if one post already exists than no charge is applied. All program controlled; SQL shouldn't be given the burden of too much of this (although you could use it to create a mustpay type field calculated off of the aggregate count from the query).

  • Users who have thanked Fou-Lu for this post:

    durangod (11-27-2012)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,151
    Thanks
    80
    Thanked 4,557 Times in 4,521 Posts
    In other words, as FouLu says, but maybe not explicitly enough, NEITHER of your A or B choices should even be considered. They are both bad ideas.

    Many-to-many tables, when properly indexed and properly used, are not only the only viable solution in a relational database environment, they are also the most efficient.

    Finding out if Joe has already paid for Feature17 is a trivial and very fast query:
    Code:
    SELECT COUNT(*) FROM
        users AS U, features AS F, userFeatures AS UF
    WHERE U.name = 'Joe'
    AND U.userid = UF.userid
    AND UF.featureid = F.featureid
    AND F.featurename = 'Feature17'
    If you already know the userid (you probably do, because Joe logged on 3 minutes ago and you stored his userid in a SESSION value) then you don't need the Users table in the query:
    Code:
    SELECT COUNT(*) FROM
        features AS F, userFeatures AS UF
    WHERE UF.featureid = $userid
    AND F.featurename = 'Feature17'
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    durangod (11-27-2012)

  • #4
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,441
    Thanks
    274
    Thanked 32 Times in 31 Posts
    Sorry for the late reply, was up all night and so had to lay down. Thank you both very very much.

  • #5
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,441
    Thanks
    274
    Thanked 32 Times in 31 Posts
    I found this and im looking at this as a model.

    Im thinking maybe my current db is a one to many so this many to many is new to me.

    image attached.

    Also any problem using this format as the name in a name field 160Credits@.60each the dot sort of concerns me, should i change it to and underscore?
    Attached Thumbnails Attached Thumbnails structure question-manytomanyexample.jpg  
    Last edited by durangod; 11-27-2012 at 01:39 PM.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,151
    Thanks
    80
    Thanked 4,557 Times in 4,521 Posts
    In that diagram, the books_authors table is *clearly* a MANY-TO-MANY table. Why would you ever think it is one-to-many??? The same ISBN can have many authorID values. The same authorID can have many ISBN values. That is the *definition* of a many-to-many table.

    ********

    The name 160Credits@.60each is using THREE illegal characters:
    (a) It starts with a digit.
    (b) It contains @
    (c) It contains .

    If you really MUST use a name like that (why???) then you will HAVE to *ALWAYS* put back tick marks around it in EVERY usage: `160Credits@.60each`

    For the life of me, though, I don't know why you would want to name a field like that.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,441
    Thanks
    274
    Thanked 32 Times in 31 Posts
    no no my bad, poor communication on my part, i put the image attached in the wrong place, i meant that i was using that as a model to do my new table structure for this buy and use credit for feature deal.

    Then on a sep note, i think mine may be a one to many. sorry about that, my bad.

    when the product description is saved it also trims it and saves it as the label name in the table so 160Credits@.60each started out to be 160 Credits @ .60 each. I didnt set it up this way but rather than rewrite the whole process (which could take weeks) i just wanted to find a happy work around. the @ and . are no problem i can take that out but the 160 ill have to noodle over maybe i can put something in front of that alpha wise.
    Last edited by durangod; 11-27-2012 at 11:19 PM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,151
    Thanks
    80
    Thanked 4,557 Times in 4,521 Posts
    Just use the back ticks as I showed `...` and you can use almost anything as a table or field name.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,441
    Thanks
    274
    Thanked 32 Times in 31 Posts
    thanks and i changed it to this format Buy1000Credits40centsea thanks for the help.


  •  

    Posting Permissions

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