CODEWITHSUNDEEP
phpmysqldatabase
Justin
Justin

Reputation: 653

Database design for price data

I have a database that stores components in a component table. There is a second table for component prices. This table has a field that maps to a component id. My price table needs to store information for a bunch of different prices (10, 100, 1000, 10K, 100K, 1M). The thing is, there is a possibility in the future to store other price types such as 25K or 50K.

As of right now, my price table looks like this:

id    component_id    type    price

where the type can take values from 1-6 currently. This is good because it will allow me to add new price types in the future very easily.

The other option is a price table that looks like this:

id    component_id    price_10    price_100    price_1000    price_10K    price_100K    price_1M

But in this case, I would need to add a new field every time a new price type is added.

I hope that people here would agree with the first method.

But using the first method, I'm having trouble displaying a page that would display all my components in my database with the 6 prices it may or may not have (should show 0 in this case). Obviously this would be simple using the second method.

This is the query I have so far:

SELECT * FROM `component` LEFT JOIN `component_cost` ON `cmpcst_component` = `cmp_id`

EDIT: I thought I would show some sample data from the component price table: The prices are a unit price for an amount X. X ranges from 10 to 1 million. So I might have something like this in my component price table:

id    component_id    type    price
1     1               1       0.50
2     1               2       0.45
3     1               3       0.40
4     1               4       0.35
5     1               5       0.32
6     1               6       0.30

Upvotes: 0

Views: 778

Answers (4)

Nonym
Nonym

Reputation: 6299

I'm not sure I follow you.. Hope you can give us the table structures, but here's one try:

SELECT c.cmp_id, ISNULL(p.price, 0) AS Price
FROM component AS c INNER JOI component_cost AS p
ON p.cmpcst_component = c.cmp_id

Maybe I don't have the column-to-table matchup right, but try that.

Upvotes: 0

david.sansay
david.sansay

Reputation: 1399

The first option its much better.

For display the data, create a view with a pivot table.

You can found help here http://en.wikibooks.org/wiki/MySQL/Pivot_table

Upvotes: 2

Matt H
Matt H

Reputation: 6532

Try something like:

select * from component c join compcst_component cc on true and c.id = cc.cmp_id;

Upvotes: 0

hakre
hakre

Reputation: 197659

Create two tables, one for components and one for compontent's prices (for a minimum count). Then you can add as many prices per component you would like. Now and in the future.

This is a variation of your first example, you just don't hardcode types but has the number that is related to a certain price.

Upvotes: 1

Related Questions