Need help with simple SQL database design.

[ibex333]

So I got a huge SQL homework.. I need to design a set of tables, build an ERD in MySQL workbench, implement my tables in Microsoft SQL Server Express and finally, create some queries that will pull needed data.

I need help with the design step, because if I mess this up, everything else will be doomed. I want to make sure I do this right.

Here's my instructions:

Specifications:

A local supermarket has decided to keep a record of customer purchases for each of its steady customers.

[b]Part A[/b]
Design a set of tables (a database) that will effectively store the data that is described below, providing easy retrieval and ensuring data integrity and validity.
Identify each table’s attributes, PK and FKs if any.
Identify which fields are mandatory or optional.

Each steady customer is asked to provide the following information: first name, lastname, address and phoneNumber. A customer can also choose to store his EBT card ID in his record.  All this data is stored in the database.
An EBT card is issued by the government and covers expenses incurred for food item purchases only. Each month the government adds money to the current balance of the EBT card. The balance is reduced each time a Customer makes food purchases and uses the EBT card to pay for those purchases.
Each time a Customer makes a purchase the system will store a record of that purchase. To view the type of data stored refer to the receipt that appears on the next page.
If the Customer pays with a creditcard the creditcard information is also stored. 
Some customers pay with cash – in which case no creditcard information is stored.
If a Customer pays using his EBT card, the purchase record will store how much money was paid using an EBT card and the EBT card balance will be reduced by the amount purchased. 
Note: 
For each Item that is purchased the system stores the item id, name of the item, unit price,  category (ALUMINUM, FRUIT, PAPERGOODS, GROCERY, POULTRY, MEAT, MILKPRODUCTS, FISH,BAKERY, CANDY), Food(Y/N), Taxable(Y/N)
When the store runs a sale the supermarket  records the following data for each SaleItem  : itemid,  date start sale, date end  sale, sale price 
Not every item is taxable.  When a customer purchases a taxable item the customer must pay tax on that item. Current tax is .089 * item price. 

[b]Part B[/b]
 Create and implement the design you have outlined in part a, using SQL Server
Populate the database with a reasonable set of data 

Receipt No:
Date:           						    Time:
Cashier Name:          				                  Register #:
Customer Name:  Mr. X
100   Ave X
Brooklyn,  NY     11229

ALUMINUM
14    Aluminum Lid Oblong #788   @ 1/.10				1.40   T
12   Aluminum Oblong  # 2062   @  6/1.00                                                         2.00  T
4     Aluminum 9 x 13 shallow #320   @1/.33				1.32  T

BAKERY
Bakery  Rye Bread                                                                                                   2.39 F
Pita									 2.19 F

CANDY/NUTS
2 Wise Potato Chips Bags @ 1/.99					1.98 F T
1 Hersheys Chocoate Bar	  @1/2.00   	    		               2.00 F T

NEWSPAPERS/MAGAZINES
Reader’s Digest								2.00 T

FRUIT
Potatoes Idaho 5 lb                                                       			1.69 F
1.57 lb  Tomatoes @ 1/.99						1.55 F
2 Onions 3 lb @ 1/.99	Sale  1/.69					1.38 F
2 Mangoes @ 1/1.19   Sale 1/.89 					1.78 F

GROCERY
2 Fleishman Margarine  @ 1/2.49 					4.98 F
Ajax Bleach Cleanser  							1.29 T
Uncle Bens Rice 5 lb							7.99 F
2 Dozen Eggs @ 1/1.39		 					2.78 F
3 Helmanns Mayo  @ 1/5.49  Sale 1 / 2.99			               8.97 F	

POULTRY
Chicken Legs 	Family Pack						15.00 F

MEAT
4lb Shoulder Roast  @1/ 8.00						32.00 F

FISH
2 lb Salmon Fillet  @ 1/7.99  						15.98 F
Total									110.67
Tax On:   11.99								     1.06
Total:									111.73
Paid with Credit Card ID:    xxxxxxxx4432                                                          111.73

So far, here's the tables I came up with.

Customer(CustomerID,FirstName, LastName, Address,PhoneNumber, EbtCardID)

Purchases(PurchaseID,Date, Total, Amount, CustomerID

Items(ItemID, ItemName, UnitPrice, Category, Food(Bool), Taxable(Bool))

SaleItem (ItemID, DateStartSale, DateEndSale, SalePrice)

I am pretty sure I am missing a few things but it's not so easy for me to see what is it that I am missing. Please help out.

Thanks very much.

 

[Ken g6]

OK, I see a few things.

Generally, I'd suggest each table should have its own index ID column. Which of your four tables doesn't? Why not?

How do you group Purchases into a receipt?

How do you link Purchases to an Item?

Edit: The receipt seems to have more fields than you have in your database. Are you expected to have all those fields?

Also, are you supposed to be keeping track of EBT balances? That's kind of insane, but possible for this assignment I guess.

 

[ibex333]

OK, I see a few things.

Generally, I'd suggest each table should have its own index ID column. Which of your four tables doesn't? Why not?

How do you group Purchases into a receipt?

How do you link Purchases to an Item?

Edit: The receipt seems to have more fields than you have in your database. Are you expected to have all those fields?

Also, are you supposed to be keeping track of EBT balances? That's kind of insane, but possible for this assignment I guess.

No, I am not supposed to keep track of EBT balances.

Regarding Indexes, I am not sure how to accomplish this yet.

I don't think I have to group purchases onto a receipt. The receipt was given to me as a form of sample data.

Thanks!

 

[Cogman]

Here is what ken is getting at. The assignment basically starts out by saying "We want to track what each individual customer is purchasing".

Right now, you can easily track how much a customer spends but what you can't really track is what a customer is spending their money on. The business would want to ask questions like "Does the customer like grapes and cheese?". At this point, there is no way to track customer preferences or shopping habits.

To answer the index question. Indexes are methods of making lookup fast for a database. Some indexes determine how the table is physically layed out in memory, others are simply lookup tables. Indexes are where your queries will be slow or fast. There are a bunch of trade offs with them so applying correct indexes is somewhat important.

The "Primary Key" index is generally the index that determines table layout.

 

[ibex333]

Here's the ERD I created with indexes expanded. Do you guys see anything I should correct?

I am trying to figure out how to track purchases...

 

[Cogman]

Any reason for splitting out the Order from the Receipt table? Looks like they are doing mostly the same thing.

Look up "many-to-many tables" for an idea on how to track items purchased in orders.

Looking at the assignment, you'll want to mark fields as nullable/not null (optional/required).

Don't use varchar, use nvarchar. You're teacher probably won't care, but I will . You can look up the differences, but ultimately the extra space used for nvarchar is worth it in the long run.

You're sale table is somewhat messed up. Why are the Dates varchar and not a "date" or "datetime" (if you want to have a sale start at the beginning of the day. Same with Sale price/total sale (actually what is total sale supposed to represent here?)

I would probably just have a 2 columned table for payment methods (id/name). This way adding new payment methods is a breeze and you aren't forever documenting that "E" = edt and "e" = extra or something silly. All in all, I personally don't like VARCHAR(1) columns, they are usually better replaced by either bit fields or separate id/description tables.

Addresses suck (not yours, just in general). A few things though to think about. How do you handle someone with an anal postman who requires a street address and PO box on all mail (my hometown Postmaster...). There is a finite number of states, you might want to just split that out to a separate id/state name table to try and avoid mistakes in spellings/etc. Same thing about varchar, just use nvarchar. And finally, this probably doesn't matter but you might think about someone from another country or a US territory (Puerto Rico).

Card number is too small as an int, it should be a bigint. (INT's max size 2billion. Card numbers are typically in the 100billion range. But you'll also what to think about the potential card "numbers" that include alphabetical characters. Maybe/maybe not. However, I would say that if you make phone numbers a varchar you probably should consider doing the same for credit card numbers.

I should say. You're table structure isn't bad and it would be workable. I'm just pointing out things that might come back to haunt someone in the future (which, for a school assignment is never )

 

[ibex333]

Alright, so I went back to the drawing board. The old design seemed wrong given the instructions.

1) I removed the "Receipt" table and instead I have the purchase table. My reasoning, is that there is no need for a "Receipt" table. Receipts are generated from each "Purchase".

2)I have a separate table for payment types.

3)Made two separate tables for cashiers and registers. (cant get the relationships to show up correctly)

 

[cabri]

Alright, so I went back to the drawing board. The old design seemed wrong given the instructions.

1) I removed the "Receipt" table and instead I have the purchase table. My reasoning, is that there is no need for a "Receipt" table. Receipts are generated from each "Purchase".

2)I have a separate table for payment types.

3)Made two separate tables for cashiers and registers. (cant get the relationships to show up correctly)

Cashiers can work different registers.
Registers can be worked by multiple cashiers

You need a table for each and a third table that contains the date/time that a given cashier was using a given register

 

[Ken g6]

Each purchase can have only one item? You don't want that to be your receipt table, do you? I liked the receipt table.

 

[cabri]

Each purchase can have only one item? You don't want that to be your receipt table, do you? I liked the receipt table.

you should be able to track the actual receipt.

In the real world; there are codes that allow the merchant to track the receipt.
And how would one know how purchase was paid?

that is all information for the receipt. The purchase (each item) should then be linked to the receipt (description, price, SKU). Common information belongs with the receipt.

 

[Cogman]

I agree with cabri. The receipt table should have all the common information about the sale (casheer, customer, date, etc, etc) while a separate table should map the items sold (and their quantities) to the receipt. Ideally, this table would also record how much each of the items sold for as item cost fluctuates with time and doing a backwards correlation to price of an item/date is a PITA but doable if you want to strictly adhere to normalization principles... This, IMO, is a good place to deviate from strong normalization. The event happened, the price that it was sold for is now set in time and will never EVER change, having an extra price lookup table will add a layer of indirection an a place where things can only be messed up (though such a table might still be useful for other reasons).