Posted by Cerebral Believer on 09/12/06 16:06

Hi everybody,

I am creating a website to sell music I write, and I want to make use of
MySQL to do things like:

Record Customer Infomation (including logins and passwords)
Record Product Information
Record Sales Informaton
Link all those information types together so I can find current totals
relating to how many products I have sold, who has bought what etc...

I have created a database using MySQL 5.0.15, and phpMyAdmin 2.7.0-pl2, so I
am not using command line interface but a web-browser based GUI. Is anyone
familiar with this interface? Basically I have created three tables in my
database (please bear in mind this is my first MySQL database).


1) Customer Information:

This holds the usual info, names and addresses etc. I am hoping that there
is a way to create a "form" (including e-mail address & password
verification) which users will fill in which will automatically be written
to the database? _I am not at that stage yet_, but will probably need some
advice when I get there as I don't have a clue how to interface my html
pages with the databse, although I am imagining it is through php. The
primary key for this table is "Member Number", this is auto incremented.

I also have a few fields I need advice on.

I have a membership field, type "set", values "Yes" or "No". Is that the
best data-type to match up with a radio button or check box offering Yes/No
options on a form?

How can I automatically calculate the total number of products a user has
ordered from me and have this figure automatically written into my database?
I think I just need this field to mirror a value that could be calculated
from my "Sales Information" table described below.

Is there a way to keep tabs of the total number of visits (by IP address, or
login), that a user has made to my site and record this in the database?

I may also need to know and be able to limit how many times a user can
download a particular file.


2) Product Information:

This holds information about specific products (eg an album, ep, single) and
each product will have its own unique record according to format (CD, or
..mp3, .ra downloads). The primary key in this table is "Catalogue Number".

I also have a few fields I need advice on here too.

I have a field "Quantity Manuafactured", basically I manufacture the
products and want to keep a running total of how many products have been
made. Can I just enter the running total into the database manually every
time more copies are made, and will any calculations that depend on this
figure always be recalculated? Or should I do another table? Anyway, I ask
this because the fields, "Quantity Sold" and "Quantity in Stock" relate to
the first field "Quantity Manuafactured". I need to know how to get the
database to perform a simple calculation, i.e. "Quantity Manuafactured" -
"Quantity Sold" = "Quantity in Stock"; and, if there is no stock I have
another field "Available" using the "set" data-type with the values "Yes",
"No" at present (I may change them to "Not Yet Released", "In Stock", "Out
Of Stock", and "Deleted/Unavailable"), can this be linked to the "Quantity
in Stock" field and automatically register "No" when the database detects
the stock level has dropped to "0"?


3) Sales Information:

This keeps an inventory of sales, every item that is sold on every format
should be recorded here. The primary key in this table is "Purchase
Number", this is auto incremented.

Questions I have on this are:

I have a date field, will this automaticallty write the purchase date to the
database? If not, how is that done?

The field "Member Number" exists both in this table and the "Customer
Information" table, is this allowable - or should both tables somehow
"share" a common field? I am using the "Member Number" in this table so
that I can calculate the number of purchases a member has made for the
"Customer Information" table.

The "Catalogue Number" and "Price" fields are also duplicated here because
they are in the "Product Information" table. Again I am not sure if this is
wise or allowable, but I want to be able to find out exactly what products
have sold, and to be able to automatically create a sales revenue over
specific time periods.

Well I think I have asked an awful lot already, so I'll stop there. If
anyone has any advice, good tutorial links, that would be great.

Thanks in advance,
C.B.

[Back to original message]