>Hi,
>I am using auto-genrated value as PK and normalize my database design to 3rd level. Here is the structure of my tables.
>In my design, my app should support multi outlet, multi barcode per item, multiple cpu, ppu for different outlet...
>
>Table: tblItem_Info
>Field: InoID (PK), ItemCode, ItemName, ...
>
>Table: tblLocation
>Field: LocCodeID (PK), LocCode, LocationDesc
>
>Table: tblItem_MOutlet
>Field: LocINoID (PK), LocCodeID(FK), INoID (FK), CPU, PPU, ...
>
>Table: tblItem_MBarCode
>Field: mBarcodeID(PK), INoID (FK), BarCode
>
>Table: tblMOutlet_PPU
>Field: MPPUID(PK), LocINoID(FK), PPU, markup, markdown ...
>
>Table: Stock_Status
>Field: StkStatus(PK), BarCodeID(FK), LocCodeID(FK), Qty
>
>From the structure above, you may see that, I have to go thru multiple tables in order to get a simple info such as PPU using ItemCode

I know, this is one of the problems with this approach. There are both advantages and disadvantages.

>tblLocation + tblItem_Info->tblItem_MOutlet->MOutlet_PPU
>
>In Order deduct or increase stock
>tblLocation + tblItem_Info->tblItem_MBarcode->Stock_Status
>
>1. I would like to know, does the design above "ok"?

It looks OK to me.

>2. Do you use databse command like SEEK, LOCATE to go thru each table to get the PK/FK, or using SQL JOIN? I found that using SQL to join 3 tables could be very slow even it is optimised IF records are alot.

You can use SELECT - SQL. However, don't join many tables at once. Your queries will be much faster if you select data from one or two tables, join the result with a third table, then join the result with a fourth table, etc.

I had one query with 9 tables, that took me 50 seconds (over the network). After splitting it into several commands, as explained above, the required time went down to 2 seconds.

If you want to test for speed, don't forget to use realistic conditions - if your users are going to access the data over a network, do at least some tests over the network. Also, try to create as many records as you expect to use under operating conditions.

>3. For certain condition, I need up to 5 tables to get certain info or operation. Does you have the same case too?

See above - I had up to 9 tables. I think it was related to cost calculations.