Best Approach for Storing One-Many Relationship - Practical Example/Dilemma

Question

I need to model an idea which can be broken down and thought of as follows:

1. BookDetails
2. BookPrices

The problem here is that you can have many prices for books and these prices are likely to change. Here's an example

BookDetails:
-----------------
ID  Name
1   Harry Potter…

This is easy enough.

Where it is more interesting is that for this one book I might have ten different prices on that day, e.g.:

BookPrices:
------------------------------------
Book_Details_Id  Kind          Price
1                SpecialOffer     10
1                BulkPurchase     20
1                Normal           30

I need to provide a list of books and all their prices in columns - something like:

BookName         SpecialOffer   BulkPurchase      Normal    
Harry Potter…              10             20          30

My question is: Should the book price table actually have all the different price types as columns? To me this is ugly and a better idea is to have each price as a row

If I use that approach I cannot think of a SQL query to generate me the result set. I have been thinking about this all morning.

EDIT: I have no leeway on calculating prices - they have to be stored down.

EDIT: This is basically the 1-n appraoch I can think of (Thanks to comment below) - Its what I actually had in mind

SELECT book.bookid, bp1.price, bp2.price FROM book JOIN bookprice bp1 JOIN bookprice bp2 ON bp1.bookid = book.bookid AND bp1.pricetype=1 AND bp2.bookid = book.bookid AND bp2.pricetype=2...

The problem is for ten prices you will be joining ten times which stinks!

Answer 1

Hi, how about this

BookDetails
BookID BookName

BookPrice
BookID PriceID PriceTypeID

BookPriceType
PriceTypeID DEscription

Answer 2

If you want to store the prices statically (instead of e.g. calculating them), use a 1-to-n-relation:

Table 1 with rows: ID Title Publisher etc.

Table 2 with rows: BookID Price

"BookID" from Table 2 will point to "ID" from Table 1, this way you can have as many prices as you like. Use JOIN (see SQL-documentation) to retrieve all prices for a specific book.

If you offer special discounts (e.g. bulk purchase 10% off, but 20% off if the customer buys more than 100 items at a time), I'd rather calcuate the price rather than storing it statically.

Answer 3

This is a basic use case for a bridge table where you have:

1 table for book info
1 table for price info
1 bridge table to join the two together for each instance of book and price.

Answer 4

I have faced this similar problem many many times. The way I look at it, is can you explicitly model a book. So for example you have 10 types of prices. Are these types static? Do they ever change? If they don't change then I prefer to have them as columns, in a pricing (wouldn't call it details). That would include an effective start date and an optional end date. This would let you handle staging price changes ahead of time.

Now if you don't know the types of prices your dealing with. An example would be a system where an end user (An Admin user most likely) would define the books and the various types of prices. This is a significantly more complex system because you would also need to let the user define the rules around when to use which price.

Update

Here's an example of using a dynamic pivot query:

create table #BookPrice (bookId int,Price money,PriceType nvarchar(30)) insert into #BookPrice values (1,10.55,'List')

insert into #BookPrice values(1,9.50,'Cost') insert into #bookPrice values (2,10.22,'List')

/Figure out which prices you need....probally not by query the table itself/ declare @priceQuery varchar(max) select @priceQuery = IsNull(@priceQuery,'') + '[' + cast(PriceType as varchar(32)) + '],' from (select distinct PriceType from #BookPrice )p

-- Remove last comma set @priceQuery = left(@priceQuery,len(@priceQuery)-1)

declare @dynquery varchar(max) set @dynquery = 'select bookId,* ' + 'from #BookPrice ' + 'pivot ( ' + ' max([Price]) ' + ' for [PriceType] ' + ' in (' + @priceQuery + ') ) as pivotTable'

exec (@dynquery)

drop table #bookPrice

UPDATE

Updated the above sample to show how if you have a book which is missing a price type that it will show up as null in the query

Answer 5

The question here is:

Wil lthe list of Book Price Types be static? or are the types of prices (Columbus Day Price, Special Superbowl XXXVIII Price, Cause I like Tuesdays Price, going to frequently appear and dissappear?

If the list is static, (say 5 or 6 different typesof Prices, then put them each in an additional column in the product table.

If the list can change (even if only slightly and/or very infrequently) then add a separate table for the prices.

  Create Table Prices (
     ProductId Integer Not Null,
     PriceTypeId Integer Not Null,
     Price Decimal(16,4) Not Null,
     Primary Key Constraint PricePK (ProductId, PriceTypeId)
     )

Answer 6

As others have mentioned, if you store the data as rows within your table you'll have more flexibility should the need for more price types arise. However using this approach most likely will require you to Pivot the data in order to output the results with one row per book with all of the various pricing options included on the same row (as your example implied). Depending upon which version of SQL Server you are using this can be relatively trivial to implement, or it can more a bit more challenging. See this SO question for more details.

If the price types are static then you might be better served simply storing the data as columns of the table instead of in rows. This makes it trivial to return the data in the form you're looking for, at the cost of being a bit more work should the rules change.

Answer 7

Edit: Sorry for misreading the question

You can use three tables here to represent you model, one for books:

CREATE TABLE Books (
BookId Int,
Name Varchar
)

one for prices:

CREATE TABLE Prices (
PriceId Int,
Amount decimal,
PriceName varchar
)

and one to join them all together (this has a flag to show the current price):

CREATE TABLE BookPrices (
BookId Int,
PriceId Int,
Current bit
)

The PriceName provides a hook for a pivot query, which will turn your rows in to columns. In my test I used the names Shelf and Online

SELECT * 
FROM (
    SELECT Book.Name BookName, Price.Amount Amount, Price.Name PriceName
    FROM Books Book
    JOIN BookPrices bp
        ON Book.BookId = bp.BookId
    JOIN Prices Price
        ON Price.PriceId = bp.PriceId
) DataTable
PIVOT(
    SUM(Amount)
    FOR PriceName IN ([Shelf],[Online])
) PivotTable

Answer 8

I think I've had similar problem.
I solved this with the t-sql construction 'pivot'
(ugliness: you have to explicitly state the columns in the query)

We're using T-SQL.
I don't know the sql dialect you're using, so it may not be applicable.

http://www.tsqltutorials.com/pivot.php

Answer 9

This answer is t-SQL specific, and could use a little refinement, but it works on SQL 2005.

Uncomment the commented lines and it'll update like MAGIC! (okay, not magic, but niftily hacky)

DROP TABLE Books
DROP TABLE Prices
DROP TABLE bookpricing

CREATE TABLE Books ( id INT, title VARCHAR(20) )
CREATE TABLE Prices ( id INT, [desc] VARCHAR(20), pricingchange VARCHAR(20))
CREATE TABLE bookpricing ( id INT, bookid INT, priceid INT, bookprice MONEY )

INSERT INTO Books VALUES (1, 'Hi Mom')
--INSERT INTO Books Values (2, 'This is another book') 
INSERT INTO Prices VALUES (1, 'Standard', '1')
INSERT INTO Prices  VALUES (2, 'Discount', '.5')
INSERT INTO Prices VALUES(3, 'HyperMarkup', '1.5')
INSERT INTO prices VALUES(4, 'Remaindered', '.1')

INSERT INTO BookPricing VALUES (1,1,1,20.00)
INSERT INTO BookPricing VALUES (2,1,2,10.00)
INSERT INTO BookPricing VALUES (3,1,3,30.00)
--INSERT INTO BookPricing VALUES (4,2,1,30.00)
--INSERT INTO BookPricing VALUES (5,2,2,15.00)
--INSERT INTO BookPricing VALUES (6,2,4,3.00)

SELECT * FROM bookpricing 

/** this bit stolen from http://www.tsqltutorials.com/pivot.php **/

DECLARE @columns VARCHAR(max)

SELECT @columns = COALESCE(@columns + ',[' + cast(id as varchar) + ']',
'[' + cast(id as varchar)+ ']')
FROM prices


DECLARE @query VARCHAR(max)

SET @query = '
SELECT * FROM (SELECT BookID, PriceID, BookPrice FROM BookPricing) AS BookTable PIVOT (SUM(bookprice) FOR priceid 
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)

Answer 10

This might perhaps do what you wish.

SELECT D.ID, D.[NAME]
, SUM(D.SpecialOffer) AS SpecialOffer
, SUM(D.BulkPrice) AS BulkPrice
, SUM(D.Normal) AS Normal
FROM (
SELECT BD.ID AS ID, BD.NAME AS [NAME]
    , CASE WHEN BP.Kind LIKE N'%SpecialOffer%' THEN BP.Price ELSE NULL END AS SpecialOffer
    , CASE WHEN BP.Kind LIKE N'%BulkPrice%' THEN BP.Price ELSE NULL END AS BulkPrice
    , CASE WHEN BP.Kind LIKE N'%Normal%' THEN BP.Price ELSE NULL END AS Normal
FROM BookDetails BD
INNER JOIN BookPrices BP ON BP.ID_BOOK_DETAILS = BD.ID) D
GROUP BY D.ID, D.[NAME]

Just make sure the column names suit yours and it should do it. Furthermore, it is ANSI, so this is no DBMS specific SQL DQL code and can be used on ORACLE, SQL SERVER, etc.