# Help with Database creation



## pcdreams (Sep 13, 2003)

I'm trying to create a database I can use for business transactions.

I've got Apache/Mysql/PHP up and running.

I've not setup a database before so I'm hoping someone can help me out.

I've figured out a few tables

Customer Table
CustomerID
Last Name
First Name
Address
City
State
Zip
Phone
E-Mail

Product
Productid
Description
Price
Inventory

Orders
OrderId
Order Details

I'm sure there will be others but this will be a good start. Ive got the customer and product tables made. I created an order table but it's not doing what I want.

basically I created it like this

OrderID
Customer ID (want it to populate fields via phone #)
Product ID (enter product ID and quantity.. it will figure price and change the db to reflect change in inventory)

I used these fields as primary keys but what happened is it imported the value from that column in the other table instead of populating the data. I know It's something I'm not seeing but I've no Idea. Not really wanting someone to do the work for me. Just point me in the right direction..


----------



## OntarioMan (Feb 11, 2007)

"Customer ID (want it to populate fields via phone #)"

Can you expand on what exactly you're trying to accomplish here? What fields are you trying to populate? Via phone #?


----------



## Gary in ohio (May 11, 2002)

For databases the important information is how you plan on querying the database thats important. looks like you want customer table, inventory and an order table. For your customer table I would have 2 addreesses, 1 for billing and 1 for shipping. Your product table is ok, but maybe a "photo" link entry would be nice. 
Your oder table is going to need more. What about date of order, I would consider something different than phone for customer ID. mysql has the ability to generate a unique number never to be repeated, use that instead. Your product id needs to be expended to include many of the inventory items. like price (at time of sale).

Since your looking at simple orders you might do a search for mysql customer database or order database and see if you cant find something already build and modify it as needed.


----------



## Harry Chickpea (Dec 19, 2008)

A couple quick thoughts. You are re-inventing the wheel. If you do a decent search you'll find a few free or shareware business programs that do what you want and a lot more you haven't thought of yet. Second thought - although databases are super-important for places like multi-site businesses, or locations where multiple people are simultaneously accessing and changing data, a small business can often be better off using some of the more advanced spreadsheet functions. The data is more transparent, easily backed-up, and you can avoid some of the pain of learning the engines. I had about a foot of thick books just on one version of Access. After a couple years, it was toss 'em out and move on.


----------



## mikellmikell (Nov 9, 2005)

If your using Excel or something easy you can probably get a lot of help here.


----------



## pcdreams (Sep 13, 2003)

Gary: That was my thought as well. I am using Mysql. I know so little about databases. Can I have the customerID number but use the phone# to populate customer address and such

And while I'm at it, how do you do a foreign key field in mysql?

Just d/l some samples to look at as suggested


----------



## Nevada (Sep 9, 2004)

Most people use phpMyAdmin to create and administrate MySQL databases, regardless of whether they have it installed on Windows or Linux. But I find it easier to create the database with my hosting control panel.

Just so we know what you're doing here, are you working on a machine at your location, remotely (a server located in a data center), or with a hosting account somewhere? Are you doing this with Linux or Windows?


----------



## OntarioMan (Feb 11, 2007)

"OrderID
Customer ID (want it to populate fields via phone #)"

You don't have to store any other Customer information in the "Orders" table other than the Customer ID. The customers phone number, location, fax number, etc. are available in the Customers tables. This is how tables are "linked". An invoice that you print may be the result of queries to many tables.

Your "productID and quantity", which you currently have in your orders table, would most likely actually be in another seperate table, something like "purchases". Tables are typically one row of data, so how would you enter multiple puchases in the same Order table? If you had a "purchases" table, it may go something like

Purchases
- purchaseID
- orderID
- productID
- product_qty


Even simple databases can get very complicated very quickly. Apache and PHP are often used to write the "interface" to the database, and what you see on one of the interface screens can be a combination of queries applied to many tables, for example, if you create an order it may create/update data in the Orders table, a purchases table, an inventory table...... 

Although spreadsheets are certainly handy and can be very powerful, they don't compare to the flexibility, speed, ease of use, reporting... capabilities of a true well designed database.


----------



## pcdreams (Sep 13, 2003)

Nevada said:


> Just so we know what you're doing here, are you working on a machine at your location, remotely (a server located in a data center), or with a hosting account somewhere? Are you doing this with Linux or Windows?


Localhost on an XP box. 

I've tried phpmyadmin and tried to figure out the workbench that now comes with mysql but I find doing it from the command line much more intuitive.


----------



## Nevada (Sep 9, 2004)

pcdreams said:


> Localhost on an XP box.
> 
> I've tried phpmyadmin and tried to figure out the workbench that now comes with mysql but I find doing it from the command line much more intuitive.


PhpMyAdmin is a good way to do things, but you need to be shown around a little for it to be useful. After selecting a database from the list on the left, click the SQL tab at the top. You can enter commands to create tables, fields, and such in that box, then click GO. The nice thing about using that utility instead of the command line is that you can enter as many commands as you like, putting one on each line, then submit them in bulk.

I also find that the graphical permissions interface saves a lot of grief.


----------



## pcdreams (Sep 13, 2003)

well I've created my tables in a way I think will work. Now I've just got to figure out how to test them.


----------



## pcdreams (Sep 13, 2003)

Nevada said:


> PhpMyAdmin is a good way to do things, but you need to be shown around a little for it to be useful. After selecting a database from the list on the left, click the SQL tab at the top. You can enter commands to create tables, fields, and such in that box, then click GO. The nice thing about using that utility instead of the command line is that you can enter as many commands as you like, putting one on each line, then submit them in bulk.
> 
> I also find that the graphical permissions interface saves a lot of grief.


Thank you for the help with that. I do like it now that I know where things are


----------

