25. februar 2005 - 17:43Der er
9 kommentarer og 1 løsning
Mange Tables frem for et stort til alle tidligere ordre.
Hej.
Sidder og bygger et database som indeholder rundt 30.000,- produkter. Folk bestiller vare og skal have mulighed for at se tidligere ordre. For at tidligere ordre skal have de korrekte informationer er jeg nød til at gemme alle data for en vare, det vil sige alle data som en vare har på det tidspunkt den bestilles. Det hjælper ikke med en Relation til hoved vare Tablet da en vare til en hver tid kan ændre informationer eller udgå og dermed slettes.
Nu er spørgsmålet så, denne tidligere ordre table bliver/kan blive voldsomt stor. Den samme vare vil jo være listet der for hver gang den er bestilt. Er der nogen fordel i dynamisk at oprette et table for hver kunde. Table navn bliver så T_KundeIdHer
Ulempen er jo at det bliver vanskeligere at sumere salg i den og den periode, lave statistikker på fortjeneste og se hvilke produkter der sælges meget af og hvilket som evt. skal droppes pga. mængde pr. ordre i forhold til administration og hele den lange smøre som angår analysering av driften.
Ved at mange fakturerings programmer oprette en fil med fakturaNr. som navn, filen indeholder da alle data for den faktura. Men det begrænser jo statistik.
Måske både gemme i et stort for administrations statistikker, og så ellers gemme i eget kunde table for hurtigere respons når kunden vil se sine tidligere ordre??????
Gode idér modtages :-) Det drejer sig om hastighed, hastighed og hastighed
Maybe I dont quite understand the exact problem but why should a product (vare) change? The only thing which I can imagine would change is the price. If anything else changes then wouldnt it be another product?
So if we assume that only the price changes then you only need to record the product number and the price on the date of sale (from product table). You should not DELETE a "udgået" vare but just mark it as "No longer in stock" (yes/no field)
Glad you saw this post, cos i know your good at this :-)
Manufactures/Suppliers are keeping current numbers and sometimes reusing them. So if a product goes out of production, the number will in some cases disapear and reapear later. Sometimes the number will quickly reapear, depends on how many products the supplier has. This is because each supplier only has a sertain amount af numbers in the registry, and the registry has a system for what kind of product it is. The system is controlled by another company than the suppliers/dealears.
The numbering system is called NRF
A product may in some cases change vital data, but keep the NRF#, therefor a product that later on needs to be returned, must be found with the data it had at the time it was sold, and for checking the coustoumers receipt against the actuall data in the database. This is not possible when the product from time to time change vital data. I therfor dont se anyother soloution than to store each item data for each item sold.
Jeg vil til hver en tid foreslå at lægge det i en stor tabel. Når du splitter det op i flere tabeller får du både svært ved at lave statistik, men du vil også få problemer med at vedligeholde stukturen i tabellen, hvis der senere skulle laves om på tabeldesignet.
Hvis der er performanceproblemer, ville jeg hellere overveje at flytte data over på en anden platform. F.eks. SQL server eller den gratis pendan: MSDE som følger med Office-pakken. Så er du ude over hastighedsproblemer.
MSDE er godt nok optimeret til 5 samtidigere brugere, men kan sagtens klare nogle flere.
It sounds as though you will need to store information on all products. An idea may be to have a product number and a version number and use these as a unique index, you could still have an autonumber as the primary key. Then you could have a yes/no field indicating if the product is active.
Now when you make an order you choose the active product and store the products primary key in the order record. THis way you will always be to link an order with a specific product version.
You could as charlotterj suggests concider MSDE as the backend, but I would first try to calculate how many records you invisage over a given period. Access CAN manage many thousands (100-200+) of records, just remember to use indexes.
Damm email system never works here on E.dk So sorry for the late reply.
The idea of a version number solves the problem to its full extend, allowing me to preserve all laws on normalisation. Think ill use an internal ProductId(InternalNRF#) This will cause some problems elsewhere in the system, mainly in the Hirachy menu used by the coustomour to find products under friendly Hiracy menu names (Hiracy menu is quite complicated with a lot off cross references), but i have a little idea to solving this. Great idea terry :-)
DB will start as access, but moved to MsSQL at the first sign of stress due to work load.
Thanks to you both, but remember to throw some answers :-)
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
