I'm developing an application using PHP / SQL Server that needs to store information on graduates from tons of different academic programs, along with information on how many were employed after graduating... This will then be queried by users, who will want to compare one program to another, so they will need to be able to generate custom tables showing the outcomes for students. Tables will be something like this:
Program Salary after 1 Year Salary after 2 Years Etc...
Engineering Lots Even more!
Philosophy Nothing Are you kidding me?
So, here's the question: should I store individual values for these data points in the database, or should I just store the raw HTML for the table row?
In other words, do I store this:
ProgramID Salary1 Salary2 Etc...
1 Lots Even More!
2 Nothing Are you kidding me?
Or do I store this:
ProgramID RowHTML
1 <td>Lots</td><td>Even More!</td>...
2 <td>Nothing</td><td>Are you kidding me?</td>
I can see arguments for both approaches... my gut tells me that storing the raw HTML will be faster - it will eliminate GOBS of PHP processing. But I'm open to other interpretations.
I would store the raw data only. this will provide the ability to change the format/layout of the page when your boss says "Make it look newer" or whatever. If you store the table data, you are stuck.
i think that storing individual values for these data points in the database will be better for managing the application , HTML is good but when you work with PHP-DATAbase is better , ... good luck dude.
So, here's the question: should I store individual values for these data points in the database, or should I just store the raw HTML for the table row?
Straight answer:
Store the data points, the html you can always recreate later, this will also make your queries way more fast.
Short answer: Store the individual data, not the HTML.
There's no benefit to storing HTML here. Reproducing the table might be a little easier, but you're also storing a lot of redundant information, and building tables dynamically is not a difficult task to begin with. It's also much easier to manipulate raw data: when you're storing salaries over time as a single row of HTML, then you can only sort by program, but if you store each year individually and control the data type (your example salaries are strings, but you should be storing integers or floats, since you know that salary is an amount of money), you have the option of sorting programs by how much money graduates make out of college, ten years down the road, etc. Also bear in mind that you might not always want to display a table: you might want to graph the income over time for each major, for example, in which case you'd need to extract the salaries from the and tags, creating more work for yourself and slowing down the application.
Simple Rule for any RDBMS , only store the data in your tables and nothing else.
This will make your data retrieval, updated and insert operations fast, simple and safe . Store data in your tables and create HTML tables on runtime. I have demonstrated here how easy and simple it is to create HTML tables when you have only data stored in your table and nothing else.
Example
I have used your given example
DECLARE @t TABLE (ProgramID INT,Salary1 VARCHAR(50),Salary2 VARCHAR(50))
INSERT INTO @t VALUES
(1,'Lots','Even More!'),
(2,'Nothing','Are you kidding me?')
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Students Salaries</H1>' +
N'<table border="1">' +
N'<tr><th>ProgramID </th><th>Salary1</th><th>Salary2</th>' +
CAST ( ( SELECT td = ProgramID,
'',
td = Salary1,
'',
td = Salary2
FROM @t
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
SELECT @tableHTML;
HTML Table Created by the above query
<H1>Students Salaries</H1><table border="1"><tr><th>ProgramID </th><th>Salary1</th><th>Salary2</th><tr><td>1</td><td>Lots</td><td>Even More!</td></tr><tr><td>2</td><td>Nothing</td><td>Are you kidding me?</td></tr></table>