Post reply

Any One Help me Please check IS my work is fully normalized??

  • June 1, 2006 at 9:17 pm

    #91955

    Sales:

     

    I am not much known about making the database .its my first experience .I have done some work  but I want that some one check my work .Is it correct or not ? OR its relations and its primary keys are correct .please help me about Sale data and check this .Is it Sale process is fully normalized or Not?

    We make a Point of sale system for mobiles. it’s a online application that’s keep all records in database.

     

    Sale process have five tables . Sales table is the main table that are relate to the other tables.

     

     

    1.      Sales

    2.      SalesReturn

    3.      SalesLineItem

    4.      Commission

    5.      Store

     

    These tables attributes are as:

     

    Sales: (saleId, mobileNo, customerName, amount, storied, saleBalance,

    SaleDate, itemCode, unitPrice, croId, commissionCode, returnId)

    Sales Return: (returnId, customerNo, dealerCode, paymentMade,

    InvoiceNo, invoiceDate, SIRNO, SIRDate)

    Store: (storeId, storeAddress, storeName)

    SalesLineItem: (itemCode, quantity)

    Commission:  (commissionCode, description, newSaleRegularCommission,

    NewSalePrepaidCommission, rechargeCommission, hardwareCommission, witholdingTax, firstSaleWithoutCommission)

     

    Sales table is the main table its primary key is the saleId

    SalesReturn:  table have returnId as a primary key and return Id represent as a foreign key in the sales table .

    Store: table primary key is the StoreId and storeId represent as a forigen key in the Sales table.

    SalesLineItem: item Code is the primary key and this represent as a forigen key in the sale table.

    Commission: In Commission table commissionCode is a primary key and commissionCode represent key as a sale table

  • June 2, 2006 at 7:13 am

    #641410

    You've got several things backwards here.

    Sales:

    Why is mobileNo in here? I would think that this would either be in a separate table (if it is used like an account number for customers) or in the SalesLineItem table (so that someone can purchase more than one mobile number at a time).

    Why is customerName stored, and not customerNo? customerNo is, IIRC, the primary key of your customer table.

    unitPrice should be in your SalesLineItem table.

    itemCode, commissionCode, and returnID should not be in this table.

    SalesReturn:

    I'm really not clear as to what business entity this represents.

    This should have saleID as a foreign key, related to the Sales table. This allows zero, one, or multiple returns per sale. The way you have it set up allows zero, one, or multiple sales per return.

    A return belongs to a sale. A sale already belongs to a customer. Storing the customerNo here is redundant.

    Store:

    This table is structured more or less correctly. For proper first normal form, you probably want to break the address out into multiple fields, such as city, and postal code. Given that, theoretically, you're likely to want to show sales by region at some point.

    SalesLineItem:

    This should have saleID as a foreign key, related to the Sales table. See the comment under SalesReturn.

    The unitPrice should be in this table. You can then quickly check the total of quantity * unitPrice across all the line items against the amount field in the Sales table.

    Commission:

    This should have saleID as a foreign key. See the comment under SalesReturn.

    Can one Commission have all these types of commissions? It seems to me that you would want a commissionType field, and a commissionAmount field. If you do get more than one commission off a single sale, you simply have more than one row in this table.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply