big table

[olivier vidal]

Le 4 juin 04, à 20:37, Ruslan Zasukhin a écrit :

> But have you specify bigger cache size ?

3*1024*1024
But I have only about twenty recordings in my test base.

> It looks to me that you ALWAYS do join of 10 tables?
>
> Just usually if you have 10 tables, you can for some task join only 
> this 2-3
> tables, for other tasks join that 2-3 tables...

E.G. :

A product can be sold in 4 stores.
In every store, the prices can be different for the same product.
He can also have there 4 different prices by product!
( That is 16 possibilities: 4 stores x 4 sort_prices)
But every product can have 2 VAT.
These 2 VAT can be different according to shops!
( That is 2 x 16: 32 possibilities)

If I normalize my relational database :

BO_PRODUCT
-Name (vstring)

BO_VAT
-Name (vstring)
-Rate (vfloat)

BO_SORT_VAT
-Name (vstring)
-Rate (float)

BO_SORT_PRICE
-Name (vstring)

BO_SORT_STORE
-Name (vstring)

BO_PRICE
- Price (vfloat)

BO_PRODUCT_SORTSTORE_SORTPRICE_PRICE
- Product_PTR
- Sort_Store_PTR
- Sort_Price_PTR
- Price_PTR

BO_PRODUCT_SORTVAT_SORTSTORE_VAT
- Product_PTR
- Sort_Vat_PTR
- Sort_Store_PTR
- Vat_Ptr


The requests :

SELECT a.name, b.name, c.name, d.price
FROM boProduct a, bo_price d, boSortPrice b, boSortStore c , 
bo_product_sortstore_sortprice_price
WHERE a.RecID=1

and

SELECT a.name, b.name, c.name, d.rate
FROM boProduct a, bo_sort_vat b, bo_sort_store c, bo_vat d, 
bo_product_sortvat_sortstore_vat
WHERE a.recID=1


The requests work slowly.


If I modify my database in a not very academic way... :

BO_PRODUCT
-Name
-Price_1_Store_A
_Price_2_Store_A
_Price_3_Store_A
_Price_4_Store_A
-Vat_1_Store_A
_Vat_2_Store_A
-Price_1_Store_B
_Price_2_Store_B
_Price_3_Store_B
_Price_4_Store_B
-Vat_1_Store_B
_Vat_2_Store_B
...........................


SELECT price_1_store_A, price_2_store_A, price_3_store_A, 
price_4_store_A...........
FROM BO_Product

The request works very quickly!

The reading is fast. If the writing of 2/3 columns in a field is fast 
also, it is very well.
If the addition or the deletion of a field is a little slower (LITTLE 
???),it is not very important, because they are operations of 
management.
It is the operations of sale that must be very fast (reading, writing 
on some columns).

Furthermore, it is much easier to manage for me. I do not feel 
comfortable with many tables.

what do you think of it?

> You can make cache size 10-20 MB and all your data will be in cache.

I do not know if I could put a lot of cache memory.
On POS PC, there is not enough memory.



thank you


olivier