Best One-to-Many Relationship Approach - Case Study / Dilemma

Question

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

• Bookdetails
• Bookprices

The problem is that you may have many book prices, and these prices may change. Here is an example

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

It is quite simple.

Where more interesting is that for this book I can have ten different prices on this day, for example:

  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: are there different price types as columns in the book price table? For me it is ugly, and it’s better to understand that each price will be like a string

If I use this approach, I cannot come up with an SQL query to generate a result set for me. I have been thinking about this all morning.

EDIT: I do not have the ability to calculate prices - they need to be stored.

EDIT: This is basically the 1st grade I can think of (thanks to the comment below). This is what I really 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 that ten prices you will enter a dozen times, which stinks!

Answer 1

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

Uncomment the commented lines and it will be updated like MAGIC! (okay, not magic, but randomly hacked)

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 is 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 2

how about this

 BookDetails BookID BookName BookPrice BookID PriceID PriceTypeID BookPriceType PriceTypeID DEscription 

Answer 3

As already mentioned, if you store data as rows in your table, you will have more flexibility if you need additional price types. However, using this approach will most likely require you to pivot the data to display the results with one row in each book with all the different pricing options included in the same row (as your example suggested). Depending on which version of SQL Server you are using may be relatively trivial to implement, or it may be more complex. See This SO Question for more details.

If price types are static, it might be best for you to just store the data in the form of columns in a table rather than in rows. This makes it trivial to return the data in the form in which you are looking, at the cost of a little more work if the rules are changed.

Answer 4

I have encountered a similar problem many times. The way I look at this is that you can clearly compose a book. So, for example, you have 10 types of prices. Are these types static? Do they ever change? If they do not change, I prefer to have them as columns, in pricing (we will not call it details). This will include an effective start date and an additional end date. This will allow you to process pricing changes in a timely manner.

Now, if you do not know the types of prices you are dealing with. An example is a system in which the end user (most likely the Admin user) will define books and various types of prices. This is a much more complex system because you must also allow the user to determine the rules when to use this price.

Update

Here is an example of using a dynamic summary query:

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

insert values ​​into the #BookPrice (1.9.50, 'Cost') insert values ​​into the #bookPrice (2.10.22, List)

/ Determine what prices you need .... probably not by request of the table itself / declare @priceQuery varchar (max) select @priceQuery = IsNull (@priceQuery, '') + '[' + cast (PriceType as varchar (32 )) + '],' from (select a separate PriceType from #BookPrice) P

- Delete the 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 a pivot table

exec (@dynquery)

drop table #bookPrice

UPDATE

The example above has been updated to show how if you have a book that does not have a price type that will be displayed as zero in the request

Answer 5

The question is here:

Is the liter list of book prices static? or are the types of prices (Columbus Price, Special Super Bowl XXXVIII Price, Reason I like Tuesday Price often appearing and disappearing?

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

If the list may change (even if it is a little and / or very rare), add a separate table for 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

If you want to save prices statically (instead of, for example, calculating them), use the 1-to-n ratio:

Table 1 with the lines: I would be the title of the publisher, etc.

Table 2 with Rows: BookID Price

The "BookID" from Table 2 will point to the "ID" from Table 1, so you can have as many prices as you want. Use JOIN (see SQL documentation) to get all prices for a specific book.

If you offer special discounts (for example, buying and selling a 10% discount, but a 20% discount if the customer buys more than 100 items at a time), I would prefer to calculate the price rather than store it statically.

Answer 7

This is a basic example of using a bridge table in which you have:

1 table for a book

1 table for price information
1 bridge to join them together for each copy of the book and price.

Answer 8

Edit: Sorry for misusing the question

Here you can use three tables to represent the model, one for books:

 CREATE TABLE Books ( BookId Int, Name Varchar ) 

one for the prices:

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

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

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

PriceName provides a hook for a pivot query that turns your rows into 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 9

I think I had a similar problem.
I solved this with the "pivot" t-sql construct
(ugliness: you must explicitly specify the columns in the query)

We use T-SQL.
I do not know which sql dialect you are using, so it may not be applicable.

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

Answer 10

It can 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 meet your requirements and he should do it. In addition, it is ANSI, therefore it is not SQL SQL SQL, but can be used in ORACLE, SQL SERVER, etc.