Table Database Advice
-
Hello everybody, i'm writing to ask an advice. In my Access db, I've many tables but the most important table is named "Products". The Primary Key for this table is the "Product Code". For every product I've to save different fields. Some fields are descriptives fields that rarely change while other fields are prices fields that change different times per day. Which is the best way to represent a product? I have these 3 ideas: 1) TABLE1 where I insert only descriptives fields and has the product code as primary key. TABLE2, where i save only prices, that has a relation one to one with the first table and share the primary key with it. 2) TABLE1 where I insert descriptives fields and an integer field at the end which is in relation with TABLE2 where I save prices. In this case the primary key of table2 will be an identity field. Also in this case it will be a one to one relation. I'm sceptic for this solution because I always add and remove products from table1 so, after 1 year it will be possible that I will have 10.000 product in my database but the primary key of TABLE2 will be a very big number like 50.000. 3) Only TABLE1 where I save every fields both desctiptives and prices fields. Which is the best solution in your opinion? In this moment i've adopted the first solution... Thank Giacomo
-
Hello everybody, i'm writing to ask an advice. In my Access db, I've many tables but the most important table is named "Products". The Primary Key for this table is the "Product Code". For every product I've to save different fields. Some fields are descriptives fields that rarely change while other fields are prices fields that change different times per day. Which is the best way to represent a product? I have these 3 ideas: 1) TABLE1 where I insert only descriptives fields and has the product code as primary key. TABLE2, where i save only prices, that has a relation one to one with the first table and share the primary key with it. 2) TABLE1 where I insert descriptives fields and an integer field at the end which is in relation with TABLE2 where I save prices. In this case the primary key of table2 will be an identity field. Also in this case it will be a one to one relation. I'm sceptic for this solution because I always add and remove products from table1 so, after 1 year it will be possible that I will have 10.000 product in my database but the primary key of TABLE2 will be a very big number like 50.000. 3) Only TABLE1 where I save every fields both desctiptives and prices fields. Which is the best solution in your opinion? In this moment i've adopted the first solution... Thank Giacomo
It depends. If a product can only ever have a single price, with no price-breaks, no customer-specific prices, etc., then store that price in the products table. If you need a more complicated pricing structure, then store the prices in a separate table, with a one-to-many relationship between products and prices. For example:
Prices:
ID (PK, Identity)
ProductCode (FK to products)
FromDate (optional)
ToDate (optional)
MinimumQuantity (optional)
MaximumQuantity (optional)
CustomerPriceGroup (optional)
Price
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
It depends. If a product can only ever have a single price, with no price-breaks, no customer-specific prices, etc., then store that price in the products table. If you need a more complicated pricing structure, then store the prices in a separate table, with a one-to-many relationship between products and prices. For example:
Prices:
ID (PK, Identity)
ProductCode (FK to products)
FromDate (optional)
ToDate (optional)
MinimumQuantity (optional)
MaximumQuantity (optional)
CustomerPriceGroup (optional)
Price
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thank you very much Richard....I think i will use your solution that is similar to my idea number 2 because, i forgot to explain, I will have to store different prices for every products....
-
Hello everybody, i'm writing to ask an advice. In my Access db, I've many tables but the most important table is named "Products". The Primary Key for this table is the "Product Code". For every product I've to save different fields. Some fields are descriptives fields that rarely change while other fields are prices fields that change different times per day. Which is the best way to represent a product? I have these 3 ideas: 1) TABLE1 where I insert only descriptives fields and has the product code as primary key. TABLE2, where i save only prices, that has a relation one to one with the first table and share the primary key with it. 2) TABLE1 where I insert descriptives fields and an integer field at the end which is in relation with TABLE2 where I save prices. In this case the primary key of table2 will be an identity field. Also in this case it will be a one to one relation. I'm sceptic for this solution because I always add and remove products from table1 so, after 1 year it will be possible that I will have 10.000 product in my database but the primary key of TABLE2 will be a very big number like 50.000. 3) Only TABLE1 where I save every fields both desctiptives and prices fields. Which is the best solution in your opinion? In this moment i've adopted the first solution... Thank Giacomo
-
Thank you very much Richard....I think i will use your solution that is similar to my idea number 2 because, i forgot to explain, I will have to store different prices for every products....
If the ProduceCode is user editable then it should never be used as a foreign or primary key, use the ProductID an identity field.
Never underestimate the power of human stupidity RAH
-
Hello everybody, i'm writing to ask an advice. In my Access db, I've many tables but the most important table is named "Products". The Primary Key for this table is the "Product Code". For every product I've to save different fields. Some fields are descriptives fields that rarely change while other fields are prices fields that change different times per day. Which is the best way to represent a product? I have these 3 ideas: 1) TABLE1 where I insert only descriptives fields and has the product code as primary key. TABLE2, where i save only prices, that has a relation one to one with the first table and share the primary key with it. 2) TABLE1 where I insert descriptives fields and an integer field at the end which is in relation with TABLE2 where I save prices. In this case the primary key of table2 will be an identity field. Also in this case it will be a one to one relation. I'm sceptic for this solution because I always add and remove products from table1 so, after 1 year it will be possible that I will have 10.000 product in my database but the primary key of TABLE2 will be a very big number like 50.000. 3) Only TABLE1 where I save every fields both desctiptives and prices fields. Which is the best solution in your opinion? In this moment i've adopted the first solution... Thank Giacomo
JackMisani wrote:
that rarely change while other fields are prices fields that change different times per day.
What happens if someone wants to return something that they bought yesterday? If they order it online two weeks ago and it ships today what price are they charged?
-
If the ProduceCode is user editable then it should never be used as a foreign or primary key, use the ProductID an identity field.
Never underestimate the power of human stupidity RAH
When I'll insert a product I'll also insert the ProductCode but then I never won't be able to modify it. I'll only be able to remove the product
-
JackMisani wrote:
that rarely change while other fields are prices fields that change different times per day.
What happens if someone wants to return something that they bought yesterday? If they order it online two weeks ago and it ships today what price are they charged?
My products will be only stocks. Every stocks has always a trading code that i'll use as PK. For every stocks i need to store 2 kinds of fields: 1) Descriptive fields that changes rarely like: description, isin code, country, expiration 2) Prices fields like: last price, minimun, maximum When I launch my app i will load my db data in my memory and i'll start to download prices from servers. I'll do calculation between the prices i've in memory and the price I receive from server and only when i'll click "Save" button i'll write data in my db....-
