Need Assistance with a SQL query
in [Programming SQL]
|
Prev: nth line where column not null
Next: CPACKET
From: Aj on 12 Apr 2010 09:50 Thank you. Yes, the table includes ISBN and the Names did not change over time just the price. "m" wrote: > Does the book title vary in time too? > > Also, the 1NF schema only has start date - the end date is only important to > make reasonable queries possible ;) > > "--CELKO--" <jcelko212(a)earthlink.net> wrote in message > news:5686ec72-3552-4c65-97ec-5e244676459e(a)e7g2000yqf.googlegroups.com... > > Your design is wrong for a history table. Can I assume that you know > > what an ISBN is? The table ought to look like this skeleton: > > > > CREATE TABLE Book_Price_History > > (isbn CHAR(13) NOT NULL PRIMARY KEY, > > book_title VARCHAR (34) NOT NULL, > > book_price DECIMAL (12,2) NOT NULL > > CHECK (book_price >= 0.00), > > price_start_date DATE DEFAULT CURRENT_DATE NOT NULL, > > price_end_date DATE, – null means current > > CHECK (price_start_date <= price_end_date), > > etc ); > > > > Time is a continuum and needs to be modeled with a pair of endpoints > > for a half-open interval. Get a copy of THINKING IN SETS; it should > > help you a lot. > > > . >
From: Iain Sharp on 12 Apr 2010 10:56 On Thu, 8 Apr 2010 14:04:31 -0700 (PDT), --CELKO-- <jcelko212(a)earthlink.net> wrote: >Your design is wrong for a history table. Can I assume that you know >what an ISBN is? The table ought to look like this skeleton: > >CREATE TABLE Book_Price_History >(isbn CHAR(13) NOT NULL PRIMARY KEY, > book_title VARCHAR (34) NOT NULL, > book_price DECIMAL (12,2) NOT NULL > CHECK (book_price >= 0.00), > price_start_date DATE DEFAULT CURRENT_DATE NOT NULL, > price_end_date DATE, � null means current > CHECK (price_start_date <= price_end_date), >etc ); > >Time is a continuum and needs to be modeled with a pair of endpoints >for a half-open interval. Get a copy of THINKING IN SETS; it should >help you a lot. ISBN is not always good primary key for a book, I have several books dating back to before the invention of the ISBN.
From: --CELKO-- on 12 Apr 2010 11:50 >> ISBN is not always good primary key for a book, I have several books dating back to before the invention of the ISBN. << Me, too! Mostly juveniles and math books from the 1800's. But the vast majority of books have an ISBN and the industry is totally dependent on it. I owned two used bookstores in the 1970-80's. What you do is create "shop numbers" for the collectibles. These are fake ISBNs which have a dummy language and publisher code. This shop number technique is also part of the UPC codes in grocery stores that have bakeries, meat markets, etc. in addition to packaged items.
From: m on 12 Apr 2010 18:35 In that case, you should not include title in this table, but have another table with title and ISBN and join the two when the title is required. Also note that a more advanced design keeps both title and ISBN (any other identifier) as names of the item - this is likely overkill for your purpose. "Aj" <Aj(a)discussions.microsoft.com> wrote in message news:E1720C26-BFFF-4D0D-B4BD-6C1999D70182(a)microsoft.com... > Thank you. Yes, the table includes ISBN and the Names did not change over > time just the price. > > "m" wrote: > >> Does the book title vary in time too? >> >> Also, the 1NF schema only has start date - the end date is only important >> to >> make reasonable queries possible ;) >> >> "--CELKO--" <jcelko212(a)earthlink.net> wrote in message >> news:5686ec72-3552-4c65-97ec-5e244676459e(a)e7g2000yqf.googlegroups.com... >> > Your design is wrong for a history table. Can I assume that you know >> > what an ISBN is? The table ought to look like this skeleton: >> > >> > CREATE TABLE Book_Price_History >> > (isbn CHAR(13) NOT NULL PRIMARY KEY, >> > book_title VARCHAR (34) NOT NULL, >> > book_price DECIMAL (12,2) NOT NULL >> > CHECK (book_price >= 0.00), >> > price_start_date DATE DEFAULT CURRENT_DATE NOT NULL, >> > price_end_date DATE, – null means current >> > CHECK (price_start_date <= price_end_date), >> > etc ); >> > >> > Time is a continuum and needs to be modeled with a pair of endpoints >> > for a half-open interval. Get a copy of THINKING IN SETS; it should >> > help you a lot. >> > >> . >>
From: m on 12 Apr 2010 18:52
While this is one method, I prefer not to put in fake values where possible - and to design systems that don't require users to do so either. I have seen too many situations where systems become impossible to maintain because of institutionalized knowledge about 'special' codes for situations not directly handled by the software (of which schema is part). Another solution, which is especially useful in the financial industry where nomenclature is _very_ disparate and sparse, is to have a table to hold basic information and another to hold names. This allows null row-space to imply null column space for queries where a certain kind of name is wanted but doesn't exist without limiting the extent of the column-space or consuming extra storage. The principal drawback of such a scheme is that SQL provides no native support for the operations required to add / change / delete the stored information, but this can be easily remedied by using some simple control code (stored procedures). This is just another example of the limitations of SQL (n0 & some N1 set operations + limited procedural control) "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:74ccab92-063c-42dc-8f80-4787d0d2c56d(a)b23g2000yqn.googlegroups.com... >>> ISBN is not always good primary key for a book, I have several books >>> dating back to before the invention of the ISBN. << > > Me, too! Mostly juveniles and math books from the 1800's. But the > vast majority of books have an ISBN and the industry is totally > dependent on it. > > I owned two used bookstores in the 1970-80's. What you do is create > "shop numbers" for the collectibles. These are fake ISBNs which have a > dummy language and publisher code. This shop number technique is also > part of the UPC codes in grocery stores that have bakeries, meat > markets, etc. in addition to packaged items. > > |