Suitable design for a database application

Question

Hi,

I have a question related to a web app that I developed in PHP, MYSQL.

basically part 1 is : I display results in the form of table say for software testing.

ID Prod_Name       Set    Date      Result  Platform
1  Alpha1          Pro1   01.01.01  PASS    2.3.1.2_OS

Now, I have divided the tables accordingly

Table Name: Results
ID, Name, Date, Result

Table Name : Set
ID, Set_Name, Prod_name

Table Name : Platform
ID,  Platform_Name, Set_Name

Now, ID in each table is an incremented value and does not relate to anything else.

My php app, starts with fetching the results from 'Results' table. Since I want SET to be displayed for every row, I am making an another connection to the database and using the query

select Set_name 
from Set 
where Prod_name = row['Name'] // row['Name'] is fetched from the results table.

now I also want to display platform which I am extracting it from Platform table using the above method i.e making another connection and passing Set_Name = row['Set_Name'] from the Set table.

Now for my application is there any other way to achieve the same result ?

Typically, for large web based applications, if data is coming from a database server is making multiple connection to a DB server a feasible option?

Please do not consider the fact that with MySQL declaring a connection statement once will do the needful but what about MSSQL server? Do we need to write a long sql statement with several joins/selfjoins/unions and use those variables all over the application?

How is the application design for this case will be?

Can anyonce give me some ideas please?

Thanks.

Answer 1

For pretty much any flavour of database, a single SELECT statement which joins three tables will perform better than three separate statements querying a table apiece. Joining is what relational databases do.

Answer 2

I may not have understood everything, but here is something similar. First, let's make an ER model.

Now, because you don't seem to like joins, create a view in the database.

CREATE VIEW v_test AS
 SELECT TestID, ProductName, TestName, Date, Result, PlatformName
 FROM Product AS p
      JOIN Test AS t ON t.ProductID = p.ProductID
      JOIN Platform AS f ON f.PlatformID = t.PlatformID;

With this in place, you can simply use:

SELECT * FROM v_test WHERE ProductName = 'Alpha1'

You may also take a look at this question/answer with a similar scenario.