适用于数据库应用程序的设计(Suitable design for a database application)

适用于数据库应用程序的设计(Suitable design for a database application)

我有一个与我在PHP，MYSQL中开发的Web应用程序相关的问题。

基本上第一部分是：我以软件测试的表格形式显示结果。

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

现在，我已经相应地划分了表格

Table Name: Results ID, Name, Date, Result Table Name : Set ID, Set_Name, Prod_name Table Name : Platform ID, Platform_Name, Set_Name

现在，每个表中的ID都是一个递增的值，并不涉及其他任何内容。

我的PHP应用程序首先从“结果”表中获取结果。 由于我希望SET显示每一行，我正在建立另一个连接到数据库并使用查询

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

现在我也想显示平台，我使用上述方法从Platform表中提取它，即进行另一个连接并从Set表中传递Set_Name = row['Set_Name'] 。

现在对于我的应用程序是否有其他方法可以实现相同的结果？

通常，对于基于大型Web的应用程序，如果数据来自数据库服务器，则正在与数据库服务器建立多个连接是一个可行的选择？

请不要考虑这样的事实，即使用MySQL声明一个连接语句一次将做必要的事情，但MSSQL服务器呢？ 我们是否需要用几个连接/自连接/联合来编写长的sql语句，并在整个应用程序中使用这些变量？

这种情况下的应用程序设计将如何？

可以随便给我一些想法吗？

谢谢。

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.

最满意答案

我可能没有理解所有的东西，但是这里有类似的东西。 首先，我们来制作一个ER模型。

现在，因为您似乎不喜欢连接，所以在数据库中创建一个视图。

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;

有了这个，你可以简单地使用：

SELECT * FROM v_test WHERE ProductName = 'Alpha1'

你也可以用类似的方案来看看这个问题/答案 。

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.