Kudler fine foods database

A description and justification for tables (permanent and temporary) used in the design of the SQL business application. Data contained in each table along with the characteristics and format and the manner in which the data supports the SQL business application.

Customer: Customer table is used to track demographic information about customers. In this tables various attributes will be related to customer information that are address, name, email, phone, birth dates, etc. This table is an important table and will be associated (relationship) with order table.

Inventory: this is the table that will contain the inventory information of Kudler Fine Foods Database. In this table various attributes (fields or column) will be Item ID, inventory ID, perishable indicator, perishable date, department ID, category, description, unit of measure, quantity on hand, unit of cost, master pack quantity, supplier ID, assembly item, substitute item and tag along items. This table has relationship with Item, Department and Supplier table.

Item: This table will contain information about each item made and delivered by Kudler Fine Foods. It will contain information about item, item type, price, labor cost, etc. This table will be associated with Order Line and Tax table. In this table taxable field will contain data types as bit i.e. Yes/No.

Order: Order table will store information about an order. In this table various attributes will be customer ID, sales person ID, pick up time, etc. This table will be associated with the Store and Customer, Sales Person and tender table. Order Line: This table will contain the information about order line and units purchased of items. In this table various field will be store code, item ID, price and unit purchased by the customer. This table will be associated with the Item and Store table.

Store: Store table will contain demographic information, hours of operation, lease for the building and the contact person information in it. This table will be associated with Order, Order Line and State table. Supplier: The supplier table tracks information on who supplies Kudler with its products and it tracks their location and contact information.

Tax: The Tax table ensures that the applicable tax being placed on each item. Tender: The tender table records the method of payments used on each order. This table will be associated with Order table. Department: The Department table contains information on the different departments and will be associated with Inventory table.

State: the State table has the state abbreviation as well as its description. Sales Person: Sales Person table tracks the different employees. It will have information about sales person employed by Kudler Fine Foods.

The above mentioned twelve tables will use data types depending upon the information they have to store. For ID purpose the above tables can use data type as Number. For storing date information   in the table the data type for fields will be Date. For storing price values the data type will be Currency or Money. And for name address, description the data type used will be Text or long text (Varchar). In case of any decision is based as Yes or No than data type Bit (Yes/No or 1 and 0) can be used.

The entire above mentioned table will be associated in the Kudler Fine Foods Database with each other. The various types of information regarding order, customer, inventory, sales person, etc will be taken from the above tables by creating views or running query and report can be generated.

Reference

Gavin, Powell (2006). Beginning Database Design. Wiley Publishing, Inc., United State of America.