|
|
Posted by strawberry on 09/13/06 19:33
Cerebral Believer wrote:
> 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.
There's too much to address here. I'd recommend you buy/borrow a couple
of books. Irritating title and format (and non-comprehensive index)
aside, I think Janet Valade's 'PHP & MySQL For Dummies' is a good
primer for the mechanics of this kind of thing. Any remaining gaps can
be plugged by online tutorials and NGs like this.
Here are a couple of links to online tools/tutorials that I've stumbled
across recently which I think you might find useful:
http://www.devarticles.com/c/a/MySQL/Building-A-Persistent-Shopping-Cart-With-PHP-and-MySQL/2/
http://www.evolt.org/article/PHP_Login_System_with_Admin_Features/17/60384/index.html
HIH
Navigation:
[Reply to this message]
|