Database design question
-
Hi, I have a table of product movements between places, Table Movements {fromId, toId, date, productId, status, firmware, etc} And a table of all the products Table Products {productId, name, status, firmware, etc} My question is if its OK this design, in particular the bolded things. In each movement some properties of the product will change (status and firmware), but the one that is really active and prevailing is the one in the Products table.. So if I want to know the prevailing status I'll ask the Products table.. and if i want to know the status that some product had last tuesday I'll ask the Movements table. Another solution could be to have a table Table ProductProperties {productPropertiesId, status, firmware} and the other two would be like: Table Movements {fromId, toId, date, productId, productPropertiesId} Table Products {productId, name, currentPropertiesId} Any comments?
-
Hi, I have a table of product movements between places, Table Movements {fromId, toId, date, productId, status, firmware, etc} And a table of all the products Table Products {productId, name, status, firmware, etc} My question is if its OK this design, in particular the bolded things. In each movement some properties of the product will change (status and firmware), but the one that is really active and prevailing is the one in the Products table.. So if I want to know the prevailing status I'll ask the Products table.. and if i want to know the status that some product had last tuesday I'll ask the Movements table. Another solution could be to have a table Table ProductProperties {productPropertiesId, status, firmware} and the other two would be like: Table Movements {fromId, toId, date, productId, productPropertiesId} Table Products {productId, name, currentPropertiesId} Any comments?
It's a little confusing... At what point does the Product table get updated to reflect its new status/firmware etc? Why does moving something from one place to another change these? Can a product exist that never moves?
I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!
-
Hi, I have a table of product movements between places, Table Movements {fromId, toId, date, productId, status, firmware, etc} And a table of all the products Table Products {productId, name, status, firmware, etc} My question is if its OK this design, in particular the bolded things. In each movement some properties of the product will change (status and firmware), but the one that is really active and prevailing is the one in the Products table.. So if I want to know the prevailing status I'll ask the Products table.. and if i want to know the status that some product had last tuesday I'll ask the Movements table. Another solution could be to have a table Table ProductProperties {productPropertiesId, status, firmware} and the other two would be like: Table Movements {fromId, toId, date, productId, productPropertiesId} Table Products {productId, name, currentPropertiesId} Any comments?
Quake2Player wrote:
In each movement some properties of the product will change (status and firmware), but the one that is really active and prevailing is the one in the Products table.. So if I want to know the prevailing status I'll ask the Products table.. and if i want to know the status that some product had last tuesday I'll ask the Movements table.
This is definitely the best solution. The other solution would only create one extra table you would need to query, making your queries needlessly complicated.
My advice is free, and you may get what you paid for.
-
It's a little confusing... At what point does the Product table get updated to reflect its new status/firmware etc? Why does moving something from one place to another change these? Can a product exist that never moves?
I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!
The last movement associated to some product determines its status/firmware. But yes, there might be products that havent been moved ever yet.
-
Hi, I have a table of product movements between places, Table Movements {fromId, toId, date, productId, status, firmware, etc} And a table of all the products Table Products {productId, name, status, firmware, etc} My question is if its OK this design, in particular the bolded things. In each movement some properties of the product will change (status and firmware), but the one that is really active and prevailing is the one in the Products table.. So if I want to know the prevailing status I'll ask the Products table.. and if i want to know the status that some product had last tuesday I'll ask the Movements table. Another solution could be to have a table Table ProductProperties {productPropertiesId, status, firmware} and the other two would be like: Table Movements {fromId, toId, date, productId, productPropertiesId} Table Products {productId, name, currentPropertiesId} Any comments?
I probably wouldn't have status and firmware in the Product table. When a new Product created I'd create the first Movement (created, bought, whatever) record for that Product.
-
I probably wouldn't have status and firmware in the Product table. When a new Product created I'd create the first Movement (created, bought, whatever) record for that Product.
Oh thats a nice solution.. Though the movements are used in other parts of the software.. so this "dummy" movements would have to have different properties.. like "from = to"
-
Oh thats a nice solution.. Though the movements are used in other parts of the software.. so this "dummy" movements would have to have different properties.. like "from = to"
You might instead have a dummy location for the from value, e.g. "thin air".