You will begin your project by designing a schema for the database you will be creating and using for the project. Your schema should have at least four tables, but can be much larger. Table size (# of columns...) can vary. There can be foreign keys between columns in different tables. Each column must have a datatype (I mean SQL datatypes here, e.g. VARCHAR) and declared to be either a key or not a key. Choose imaginative and meaningful column and table names.
The following is a description of what the database should store. Certain aspects of the domain will be mandatory. In addition, you are expected to invent a few other attributes of interest to model in your database. The description of the domain will be given informally, and you will need to prepare a database schema that models the domain.
An invoice is a record of a business transaction, and hence the billing and invoice database has a record on all your transactions. It is thus probably the most crucial component of your business. There is typically a invoice for every order that a customer places. This invoice records all the information regarding the items being purchased as well as information about the customer.
The following are the mandatory aspects of the domain that you need to model
Your schema must model the details of all the purchases that are made by customers, and also information about the customers themselves. Here are some of the data entities of interest.
Invoice: Each invoice is identified by a unique number or id for tracking purposes. The invoice has an order date that corresponds to the date the order was placed. A single order from a customer can include one of more distinct products (different books and CDs). Each order has a buyer and a possibly different customer to whom the items are being shipped to (in case it is a gift). There is a method of shipping with every invoice. The total price of a purchase can comprise of various parts, such as the total price of all the items being purchased, discounts if any, the cost of shipping, and local taxes, if any. There also has to be information about the method of payment. If the payment is made by credit card them the usual credit card details have to recorded.
Items: Each item purchased has to be identified by the corresponding invoice number. Further the product that is being purchased has to be identified by its unique identifier (e.g., the ISBN in the case of books). The item name is usually enlightening (book or CD title). Since the customer might order more than one copy of the book or CD, the quantity of the order is important, as well as the price per unit of the item book.
Other information that needs to be modeled includes the warehouse from which the items are going to be shipped (all from possibly different warehouses), and an expected delivery date.
Customer: Each customer (either the person who bought an item or to whom an item is being shipped) has a name and an address. The address can be sub-divided into the street address, city, state and zip code. In addition there might be contact information such as the phone number and email address.
Optional modeling: In addition to the mandatory aspects of the domain, you should add about 4 attributes of your own. You are welcome to visit any of your favorite online stores for ideas on additional attributes to model in your application.