Order Database

The purpose of this database is to create a simple and efficient system to order items and place them in a record to search for at a later time. The requirements that were needed for this was a paper order example, a spreadsheet for normalization, a document for database annotation, and phpmyadmin for creating the database itself. The steps to this is to first make a spreadsheet with all the data that is on the order form. Once this is done we must do normalization and make it more efficient without duplicate entries wasting space such as all of the client info. Instead make a second table with the added field of the user ID which will be unique without any two people having the same ID and put in the user info but not any of the product info which will be kept in a separate table with the user ID field instead of all of the client info. In this case we could take this one step further and make a third table that has all the product info with a product ID which will replace the product info in the second table reducing the total fields in the second table to four fields instead of the initial eleven fields. The database notation is done by first distinguishing what type of normalization level is being used (UNF, 1NF, 2NF, etc) and then stating the name of the table followed by a list of the fields used separated by commas in all lowercase in between two round brackets. In the case of being at a higher normalization level with more than one table separate them with a line and type the new tables name and repeat until all tables have been annotated. Once this was done we started up xampp and went to the phpmyadmin URL and created a new database with three tables with the appropriate amount of fields in each and then going into the designer tab and setting up relationships between the UserID and also the productID as foreign keys in the orders table to link between the three tables. The only issues encountered were that if you created the wrong primary key field we initially believed we cannot delete the primary key and we would be required to make a new field and delete the old one until we discovered that if we type in the SQL code tab

"ALTER TABLE `customers`
  DROP PRIMARY KEY;"

where the word “customers” is the table name desired which would result in the primary key being removed without having to delete and remake the field. To test that the database worked we inserted the data provided on the order form into the database doing the user and products before the order table.