If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

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)

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

Originally Posted by durangod

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

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.