Database Design Help

[aceriker]

Hi guys, I'm fairly new to PHP and mySQL, using a book to help me learn. I want to create a small site to help me improve my skills, but I'm a bit stuck on the database design part.

I want to be able to list various public companies and show the profits of the companies from the last 10 years.

For example:

Company Name // CEO // Founded // 2009 Profit // 2008 Profit // ... // 10 Year Average Profit

Apple // Steve Jobs // 1981 // $10.3 bn // $7.8 bn // ... // $3.58 bn

// = column separators :)

As far as I can tell, I need to have two tables in the database, one for the company data and one for the earnings:

Table 1: Company

• company_id
• Company Name
• CEO
• Founded
• Website
• Share Price

Table 2: Earnings

• earnings_id
• company_id
• Year
• Earnings

Bold = primary key for that table

My question is, is this the best setup for my tables? I guess I then need to learn a lot more about table joins to actually display the data in the format I gave above.

Yeah its fine, and you make that company_id as foreign key in Earnings r8.

[urtrivedi]

To me it seems to be fine. You may continue using this design. You may join tables using following query

select a.company_id, a.company_name ,a.CEO, a.Founded, a.Website,  a.Share_Price ,
b.year, b.earning
from company a left outer join earnings b on a.company_id=b.company_id

[aceriker]

Hey, thanks a lot for the input, the foreign key and the SQL query worked well. Here's the demo:

http://www.peterclaridge.com/company_demo/index.php

I modified the SQL query to sort by date founded. I put the actual query as a comment in the source code if you want to view it.

Instead of listing each company 4 times and to get the correct data in the 2008/7/6 earnings columns, do I have to do a new SQL query for each year or modify the existing one?

Little bit confused, why it comes 4 records for the same company? only the earnings is the difference. Whether it shows irrelevant output?

[urtrivedi]

YOU may try following query

SELECT a.b_id, a.company_name AS name, a.ceo, a.founded, a.website, b.year, 
sum(case when b.year=2006 then b.earnings else 0 end ) y2006,
sum(case when b.year=2007 then b.earnings else 0 end ) y2007,
sum(case when b.year=2008 then b.earnings else 0 end ) y2008,
sum(case when b.year=2009 then b.earnings else 0 end ) y2009
FROM business a left outer JOIN earnings b on a.b_id=b.b_id 
group by a.b_id, a.company_name, a.ceo, a.founded, a.website ORDER BY (founded)

[aceriker]

Great! Check it out, it worked, thanks. I will read up on the SUM and GROUP BY functions, I just need pushing in the right direction sometimes. I guess instead of hard coding the years 2009/8/7/6 I could include them as variables?

sum(case when b.year=$year1 then b.earnings else 0 end ) y2006,

The next job I'm going to tackle is to build a form to add, edit and delete records from the database. Maybe let the visitor sort the columns themselves at a later date - but that looks a little bit complex.

Thanks for the help!

Good Work urtrivedi!