Database design
-
I'm starting a new project next year (business application) I will be the only one working on it from a start, but my SQL knowledge is limited (not the SQL syntax, but the database design) Until now I've sorted my problems on the way, but that's not a long term solution that I'm looking for, so can anyone point me to some good articles (or books in e-book format as I live in a country with limited book availability) Thanks in advance
-
I'm starting a new project next year (business application) I will be the only one working on it from a start, but my SQL knowledge is limited (not the SQL syntax, but the database design) Until now I've sorted my problems on the way, but that's not a long term solution that I'm looking for, so can anyone point me to some good articles (or books in e-book format as I live in a country with limited book availability) Thanks in advance
progress-database-design-guide/[^] en.wikipedia.org/wiki/Database_design www.databasedesign-resource.com well u should consider getting urself acquianted with basic db design principles and normalization (uptp 3NF atleast). wikipedia can also be helpful for u and Google offcourse is also there to help u out. Hope that helps u best of luck
Rocky You can't climb up a ladder with your hands in your pockets.
-
progress-database-design-guide/[^] en.wikipedia.org/wiki/Database_design www.databasedesign-resource.com well u should consider getting urself acquianted with basic db design principles and normalization (uptp 3NF atleast). wikipedia can also be helpful for u and Google offcourse is also there to help u out. Hope that helps u best of luck
Rocky You can't climb up a ladder with your hands in your pockets.
I've only been in database design a few years, and have learned a lot of things the hard way. But I'm sure there a lot of other things I haven't learned yet, or am *about to* learn them the hard way. The link you provided,
Rocky# wrote:
progress-database-design-guide/[^]
says in one place: "primary keys with business meaning as well as composite keys are a bad idea." This I did not know, and even now am not convinced of. I have tended to make my designs exactly the opposite. Let me give an example of a table I recently designed.
CREATE TABLE tblScheduledInventory(
asofDate SMALLINT -- number of days since Jan 1, 2000
, partsGroupID SMALLINT -- REFERENCES tblPartsGroups
, locationID SMALLINT -- REFERENCES tblLocations
, statusCode SMALLINT -- "installed", "spare", etc.
, inventoryTypeID SMALLINT -- "active", "in-reserve", etc.
, CONSTRAINT pkScheduledInventory PRIMARY KEY
(asofDate,partsGroupID,locationID,statusCode,inventoryTypeID)
, cnt INT
);I need the five fields within the PK to be compositely unique. In addition to that, I KNOW that during use of this table, the records are going to be "marched-thru" in a date-order, and for each date, the records will be traversed in a parts-grouping order. Therefore, I WANT the clustered index (the actual ordering of records within storage) to be by asofDate, then by partsGroupID. This should produce faster queries since all the records of interest will be clustered near one another, and not scattered all over the hard-drive. The only way to achieve this (that I know of) is to make the primary key a composite index of the items that need to be unique, with the most important item being first in the list. Perhaps someone could shed some light on *why* this might be a bad design practice. Examples would really help me understand. BTW, in other readings I've discovered there are alternate opinions regarding this point. Some say the way I do it is OK.
David --------- Empirical studies indicate that 20% of the people drink 80% of the beer. With C++ developers, the rule is that 80% of the developers understand at most 20% of the language. It is not the same 20% for different people, so don't count on them to understand each other's code. http://yosefk.com/c++fqa/picture.html#fqa-6.6 ---------
-
I've only been in database design a few years, and have learned a lot of things the hard way. But I'm sure there a lot of other things I haven't learned yet, or am *about to* learn them the hard way. The link you provided,
Rocky# wrote:
progress-database-design-guide/[^]
says in one place: "primary keys with business meaning as well as composite keys are a bad idea." This I did not know, and even now am not convinced of. I have tended to make my designs exactly the opposite. Let me give an example of a table I recently designed.
CREATE TABLE tblScheduledInventory(
asofDate SMALLINT -- number of days since Jan 1, 2000
, partsGroupID SMALLINT -- REFERENCES tblPartsGroups
, locationID SMALLINT -- REFERENCES tblLocations
, statusCode SMALLINT -- "installed", "spare", etc.
, inventoryTypeID SMALLINT -- "active", "in-reserve", etc.
, CONSTRAINT pkScheduledInventory PRIMARY KEY
(asofDate,partsGroupID,locationID,statusCode,inventoryTypeID)
, cnt INT
);I need the five fields within the PK to be compositely unique. In addition to that, I KNOW that during use of this table, the records are going to be "marched-thru" in a date-order, and for each date, the records will be traversed in a parts-grouping order. Therefore, I WANT the clustered index (the actual ordering of records within storage) to be by asofDate, then by partsGroupID. This should produce faster queries since all the records of interest will be clustered near one another, and not scattered all over the hard-drive. The only way to achieve this (that I know of) is to make the primary key a composite index of the items that need to be unique, with the most important item being first in the list. Perhaps someone could shed some light on *why* this might be a bad design practice. Examples would really help me understand. BTW, in other readings I've discovered there are alternate opinions regarding this point. Some say the way I do it is OK.
David --------- Empirical studies indicate that 20% of the people drink 80% of the beer. With C++ developers, the rule is that 80% of the developers understand at most 20% of the language. It is not the same 20% for different people, so don't count on them to understand each other's code. http://yosefk.com/c++fqa/picture.html#fqa-6.6 ---------
thanks for ur response David its really nice! and uknow I believe in one thing: "Rules always have exceptions"... Generally they do say n its true to quite some extent that one should avoid making unnecessary composite PKs when a single attribute PK shud do. Well actually the thing is that having unnecessary composite PKs can make ur design vulnerable to having some anomalies u know it can fail satisfy 2nf. But still it all depends on ur particular scenario bcz in a way ur scenario can be peculiar to many. I think ur idea there is quite intelligent and looks quite good to me mate!
Rocky You can't climb up a ladder with your hands in your pockets.
-
progress-database-design-guide/[^] en.wikipedia.org/wiki/Database_design www.databasedesign-resource.com well u should consider getting urself acquianted with basic db design principles and normalization (uptp 3NF atleast). wikipedia can also be helpful for u and Google offcourse is also there to help u out. Hope that helps u best of luck
Rocky You can't climb up a ladder with your hands in your pockets.
-
Thank you for your help :) I've been quite new into this - and since I've lost my mentor, any help is more than welcome.