This post is mainly about Access. I just hope I can get some suggestion of about my database table hierarchy. This topic is related with my previous post. First I will explain how did I set up my database. (Food Menu Table): (No Primary Key) Dish ID Dish Type Dish Name Price Discription (Temp Order Table) Dish ID Dish Name Quantity Subtotal (Orders History Table) Order Number (Primary Key) Order Total Price Order Time (Revenue Table) Order Number (Primary Key) Dish Name Quantity The bit that's confusing is the right to set up the (Order History) and (Revenue Table), do u think I should combine these two into one or leave it as it is? It is was 'Justmade' told me Database Normalisation? Should I apply this method to make the dbase more efficiency Please feel free to give any suggestion, if u think there are fields missing or bad design. Thanks for advancing :)

In my opinion,I'll remove the [Orders History Table],just run the sql like ("select sum....") instead of another table designed to record the history.The table structure will be more simple. Maybe it will cause another mistake.Just suggestion.

thanks of your suggestion. I think I need a much clear idea of what I need to do first.    

引言: thanks of your suggestion. I think I need a much clear idea of what I need to do first. >< face="Verdana, Arial, Helvetica"> A little question!!! Could you read the traditional chinese ? It'll be more efficient using the chinese to communicate with you. I can read in english,but the writing is poor,so sorry. 發表人 -

OF COURSE >

我建議你可以喝杯熱牛奶,先去睡一覺,讓腦袋休息一下. 如果真的睡不著,找一張紙,把你的困擾寫出來, 慢慢寫,塗鴉劃劃也可以. 說不定可以劃出flow char出來... 寫寫字,劃劃圖,也可以安定一下心神. 我隱約覺得你需要的是system analyze.

Absolutely! yes I do need a system analyze. This is a school project and I suppose to do lots of analysis and design before I start programming, becasuse it is kinda too late now, I need to jump ahead. That's why I just asking about dbase normalisation, because I really need to be clear about my dbase structure

As a student of the field, you have to learn the knowledge of design and programming as well as time management. One point to learn is why it is too late? The other point to learn is what to do when it is already too late. Continue with a bad but workable design and finish the project? or spend all the time asking for help and failed to finsih the project? Hints : 1. Dish ID of Food Menu Table should be set primary key 2. Try to use ID instead of Name to do table linkage 3. When you can look up some field in another table, you dont need to include those field. 4. Fields which can be simply caluated is not necessary

is that mean I don't need Subtotal and Total? if yes, how should save those fields? Also, is that mean I don't need duplicated field among all the tables (my case the temp table is an exception). Thanks

Yes, after your database design is normalized, duplicated fields and calculated fields will no longer exists. However, normalization is not nessary to create your project. So, you can stick with your own table structure except you understand how to manage your project after removing those fields.

well i can try. I have some knowledge on normalisation. but, I am unsure how to relate some table together using Access, can u possibly give me an example using my case?

If I were you, my design will be such :    (Food Table): Dish ID (Primary Key) Dish Type Dish Name Price Discription    (No Temp Order Table)    (History Table) Order Number (Primary Key) Order Time    (Detail Table) Order Number (Primary Key) Dish ID (Primary Key) Quantity    DataSet in Delphi : tFood : ADOTable tHistory : ADOTable tDetail : ADOTable qOrder : ADOQuery --SQL :

select OrderNumber, OrderTime, DishID, DishName, Description, Price, Quantity, (Price * Quantity) as SubTotal
  from Detail D, History H, Food F 
  where D.OrderNumber = H.OrderNumber 
    and D.FoodID = F.FoodID
    and OrderNumber = :OrderNumber

qDeleteDetail : ADOQuery --SQL :

Delete from Detail where OrderNumber = :OrderNumber

When Order : 1. create an order number, with time auto inserted to tHist 2. User Input / Select Dish ID and Quantity, which is inserted into tDetail together with the Order Number 3. repeat 2 until all orders is inputed 4. If User press confirm and print button , open the qOrder query and print using those data: qOrder.Parameters[0].Value := tHist.FieldByName('OrderNumber').Value; qOrder.Active := true; 5. if User press cancel then execute qDeleteDetail to delete all items in the order then delete the order record. qDeleteDetail.Parameters[0].Value := tHist.FieldByName('OrderNumber').Value; qDeleteDetail.ExecSQL; tHist.Delete; You can print any order later in any time using the qOrder query, what you have to do is to supply the order number. For example, you can place a edit1 in form and when click print button : qOrder.Parameters[0].Value := Edit1.Text; (if order number is number, you may need to change to string to integer) qOrder.Active := true; To get Order totals you can use SQL select OrderNumber, Sum(Price * Quantity) as OrderTotal from Detail D, Food F where D.FoodID = F.FoodID group by OrderNumber I dont recommend you change your own design becasue you WILL encounter new programming problems which you do not have time to deal with. However, such information can help your presentation. 發表人 - Justmade 於 2003/04/04 11:04:31

sounds like a great Great design. I have a question, if there are two primary keys in the detail is that mean there shouldn't be duplicate 'Combination' among those two. e.g Order No. 686 Item Code: 001 Order No. 686 Item Code: 002 OR Order No. 686 Item Code: 001 Order No. 699 Item Code: 001 because if this is not true, there will be duplicate values. Also, if I set Order No. as 'Auto Number' in Access, is there anyway to make it become a 4 digi auto number? like '0001', '0002' thnx, Justmade, u have been like a wizard to me :)

I like to try to implement your idea. I like to tell you the way my program works now see if I need to start again, or just make some changes to my current interface. This is my current design: 1. I set up two DBGrid: A and B 2. A is linked to temp order table (ADOTable) 3. B is linked to the menu dbase (ADODataset) 4. 1 Tedit and 1 Tdbedit are created 5. Both are set when press enter, they will *****.SetForce to each other (This is to increase efficiency, without using mouse clicking fields everytime) 6. In Tedit1. i did: procedure TMain.Edit1Change(Sender: TObject); begin ADODataSet1.close; ADODataSet1.CommandText := 'Select * from menu where id = "' edit1.text '"'; ADODataSet1.open; end; 7. Tdbedit is linked to the Temp Order Table 'Quantity' Justmade, do u think I can still implement your idea in my program?

8. On exit: I did : procedure TMain.Edit1Exit(Sender: TObject); begin ADOTable1.Insert; ADOTable1.FieldValues['Dish ID']:= ADODataset1.FieldValues['ID']; ADOTable1.FieldValues['Dish Name']:= ADODataset1.FieldValues['Dish Name']; ADOTable1.Post; end;

Take a look at my program interface:    http://delphi.ktop.com.tw/topic.php?TOPIC_ID=28153    Give me some feedback or suggestion if u like 

Should I use Tlistview or TMemo component to replace the Tdbgrid(Temp Table) I currently use? thanx