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

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"?

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.

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


Pls advise
Thank you