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.

I think you've already answered at least a good chunk of your own question:

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).

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:
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:

Sorry for the late reply, was up all night and so had to lay down. Thank you both very very much. :)

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?

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.

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.

Just use the back ticks as I showed `...` and you can use almost anything as a table or field name.

thanks and i changed it to this format Buy1000Credits40centsea thanks for the help. :)