JPro JPro - 1 year ago 47
PHP Question

Suitable design for a database application

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

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?


Answer Source

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.

 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.