Product dataabase
-
Hello! I have the following problem: I need tips on structuring a product database. This will be a database of software products and what I specifically need help with is the following: Say you have a software product in 4 editions: Free, Lite, Standard and Enterprise. I need to model all four kinds possibly in one table and not just that, I also need to make sure that changes are trackable, i.e. if I put out a new minor version for one of the editions, I want to be able to track that there's been a change and what the change has been. Is it clear? Can you please advise and help? Thank you! A
-
Hello! I have the following problem: I need tips on structuring a product database. This will be a database of software products and what I specifically need help with is the following: Say you have a software product in 4 editions: Free, Lite, Standard and Enterprise. I need to model all four kinds possibly in one table and not just that, I also need to make sure that changes are trackable, i.e. if I put out a new minor version for one of the editions, I want to be able to track that there's been a change and what the change has been. Is it clear? Can you please advise and help? Thank you! A
This isnot answerable in a forum post, you are going to have to do some research, here is some google foo[^] some where there is an entire collection of sample schemas for different industries. This may get you started but you are still going to need to do a lot of reading.
Never underestimate the power of human stupidity RAH
-
Hello! I have the following problem: I need tips on structuring a product database. This will be a database of software products and what I specifically need help with is the following: Say you have a software product in 4 editions: Free, Lite, Standard and Enterprise. I need to model all four kinds possibly in one table and not just that, I also need to make sure that changes are trackable, i.e. if I put out a new minor version for one of the editions, I want to be able to track that there's been a change and what the change has been. Is it clear? Can you please advise and help? Thank you! A
DeerBear wrote:
I need tips on structuring a product database.
Learn the trade before you learn it's tricks. A decent database-design is normalized.
DeerBear wrote:
Free, Lite, Standard and Enterprise.
I need to model all four kinds possibly in one table and not just that, I also need to make sure that changes are trackableHaving four different licenses doesn't complicate design - it's not even clear whether or not the licenses will differ in terms of their datastructure. Tracability has nothing to do with the design; when you change it, make a note on a piece o' paper describing what you changed. Do that every time, and you'll have a list of changes.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
DeerBear wrote:
I need tips on structuring a product database.
Learn the trade before you learn it's tricks. A decent database-design is normalized.
DeerBear wrote:
Free, Lite, Standard and Enterprise.
I need to model all four kinds possibly in one table and not just that, I also need to make sure that changes are trackableHaving four different licenses doesn't complicate design - it's not even clear whether or not the licenses will differ in terms of their datastructure. Tracability has nothing to do with the design; when you change it, make a note on a piece o' paper describing what you changed. Do that every time, and you'll have a list of changes.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Hi! I am not new to databases, far from it, I was more looking for speculative insight on how best to do things. The reason for that is that this product will also be doing quite a bit of OLAP and that usually means your database has to be de-normalized. The more I look into it, though, the more I realise there is no way to get minimal de-normalization so that I can use the same table for two purposes without putting an overloaded burden on development. The tracking will have to be done in AUDIT tables with INSERT triggers. All of the solutions I could think of to avoid this are, effectively, unusable. HOWEVER, if you happen to have ideas on how to do that, I would be interested to know :) This is a sample of my structure(to give you an idea of the path I am taking):
CREATE TABLE PRODUCTS (
ID INTEGER NOT NULL,
PRODUCT_NAME VARCHAR(255) NOT NULL,
PRODUCT_DESCRIPTION BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL,
DOWNLOADABLE INTEGER NOT NULL,
PRODUCT_DISCONTINUED INTEGER NOT NULL
);CREATE TABLE PRODUCT_DETAILS (
ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
HIGH_VERSION INTEGER,
LOW_VERSION INTEGER,
EDITION VARCHAR(255),
DOWNLOADABLE INTEGER NOT NULL
);CREATE TABLE PRODUCT_HISTORY (
ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
VERSION_HIGH INTEGER DEFAULT 1 NOT NULL,
VERSION_LOW INTEGER DEFAULT 1 NOT NULL,
EDITION VARCHAR(255) DEFAULT '' NOT NULL,
DISCONTINUED INTEGER DEFAULT 0 NOT NULL,
DOWNLOADABLE INTEGER DEFAULT 1 NOT NULL
);Oh and before someone starts whining about the lack of primary or foreign keys: this is Firebird, those elements are part of ALTER TABLE statements. They're there but unreported for brevity :)
-
Hi! I am not new to databases, far from it, I was more looking for speculative insight on how best to do things. The reason for that is that this product will also be doing quite a bit of OLAP and that usually means your database has to be de-normalized. The more I look into it, though, the more I realise there is no way to get minimal de-normalization so that I can use the same table for two purposes without putting an overloaded burden on development. The tracking will have to be done in AUDIT tables with INSERT triggers. All of the solutions I could think of to avoid this are, effectively, unusable. HOWEVER, if you happen to have ideas on how to do that, I would be interested to know :) This is a sample of my structure(to give you an idea of the path I am taking):
CREATE TABLE PRODUCTS (
ID INTEGER NOT NULL,
PRODUCT_NAME VARCHAR(255) NOT NULL,
PRODUCT_DESCRIPTION BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL,
DOWNLOADABLE INTEGER NOT NULL,
PRODUCT_DISCONTINUED INTEGER NOT NULL
);CREATE TABLE PRODUCT_DETAILS (
ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
HIGH_VERSION INTEGER,
LOW_VERSION INTEGER,
EDITION VARCHAR(255),
DOWNLOADABLE INTEGER NOT NULL
);CREATE TABLE PRODUCT_HISTORY (
ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
VERSION_HIGH INTEGER DEFAULT 1 NOT NULL,
VERSION_LOW INTEGER DEFAULT 1 NOT NULL,
EDITION VARCHAR(255) DEFAULT '' NOT NULL,
DISCONTINUED INTEGER DEFAULT 0 NOT NULL,
DOWNLOADABLE INTEGER DEFAULT 1 NOT NULL
);Oh and before someone starts whining about the lack of primary or foreign keys: this is Firebird, those elements are part of ALTER TABLE statements. They're there but unreported for brevity :)
DeerBear wrote:
The reason for that is that this product
will also be doing quite a bit of OLAP and that
usually means your database has to be de-normalized.So not a complete newbie then - excellent. Most OLAP solutions use a different database than the production DB and the data is denormalised periodically (daily) into said OLAP Db. While I don't know firebird I would not use the audit process/triggers spit to retain/create your history. I would build it into the business process where the application generates the history records (you may not want trivial changes to the data to create a history record).
Never underestimate the power of human stupidity RAH
-
DeerBear wrote:
The reason for that is that this product
will also be doing quite a bit of OLAP and that
usually means your database has to be de-normalized.So not a complete newbie then - excellent. Most OLAP solutions use a different database than the production DB and the data is denormalised periodically (daily) into said OLAP Db. While I don't know firebird I would not use the audit process/triggers spit to retain/create your history. I would build it into the business process where the application generates the history records (you may not want trivial changes to the data to create a history record).
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
Most OLAP solutions use a different database than the production DB and the data is denormalised periodically (daily) into said OLAP Db.
I am not an OLAP expert, by far, but in the very few experiences I've had where we needed cubes, I've found that you don't always need another db. What I am trying to achieve is a middle ground between a complete data warehouse and a "normal" database. I don't mind, in this resepct, to lose less meaningful data, as far as the most important data is dealt with appropriately. I would like to add some new tables, prefixed by OLAP_ so that I know those are related to my cubes, and do something like this:
CREATE TABLE OLAP_PROD_HISTORY(
ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
VERSION_HIGH INTEGER DEFAULT 1 NOT NULL,
VERSION_LOW INTEGER DEFAULT 1 NOT NULL,
EDITION VARCHAR(255) DEFAULT '' NOT NULL,
DISCONTINUED INTEGER DEFAULT 0 NOT NULL,
DOWNLOADABLE INTEGER DEFAULT 1 NOT NULL,
CHANGE_DATE DATE NOT NULL,
CHANGE_TYPE INTEGER NOT NULL DEFAULT 0
)The original idea though was trying to keep everything in the ordinary tables but I do not think that's doable in any easy way - unless someone comes up with a brilliant idea, that is! Do you see my point? Regards, A
-
Hello! I have the following problem: I need tips on structuring a product database. This will be a database of software products and what I specifically need help with is the following: Say you have a software product in 4 editions: Free, Lite, Standard and Enterprise. I need to model all four kinds possibly in one table and not just that, I also need to make sure that changes are trackable, i.e. if I put out a new minor version for one of the editions, I want to be able to track that there's been a change and what the change has been. Is it clear? Can you please advise and help? Thank you! A
I must be missing something because that seems pretty trivial. (If you want to track actual changes to code, rather than just something like release notes, then you should be using a version control system.) And why does it matter if it is OLAP or denormalized? How many entries entries based on actual business cases (not developer fantasy) will it contain?
-
I must be missing something because that seems pretty trivial. (If you want to track actual changes to code, rather than just something like release notes, then you should be using a version control system.) And why does it matter if it is OLAP or denormalized? How many entries entries based on actual business cases (not developer fantasy) will it contain?
jschell wrote:
How many entries entries based on actual business cases (not developer fantasy) will it contain
Damn missed that! This whole thing will be so trivial it does not matter how he builds the bloody thing!
Never underestimate the power of human stupidity RAH