需要帮助开发DB逻辑 [英] Need help in developing DB logic

问题描述

这是我的一个小项目 - 航空预订系统 - 让我叫这家航空公司FlyMi：我有一个数据库（不确定哪一个，我的朋友想和MongoDB一起去）。

Anyhoo，这是我的要求：
我有一个表，其中有航班的详细信息 - 航班号，日程表等。我将使用这个表执行各种操作 - 预订，取消，修改

在那里我被困住：对于桌面应用程序和Web应用程序 - 我提供一个选项来选择座位。这意味着我必须跟踪哪些座位预订，哪些不是。假设我有一个UI，显示座位为
红色 - 预订
绿色 - 未预订。

这一切 - 对于每一个航班。我的问题是：你认为对于该航空公司的每次航班，如何跟踪座位预订的最有效的方式是什么？

当前想法：保留名为passenger的表格，其中包含跟踪所有乘客的所有详细信息，如姓名，地址等，并保留乘客ID ，这样，前4个字符是航班ID，最后2个字符是他们选择的座位号码，中间有随机数字（我说随机，因为我认为这是无关紧要的）。所以，对于任何航班，如果我必须找出未预订座位的数量，我将必须扫描每个乘客，谁已经预订，谁在该航班预订。我认为这是真正低效的。

解决方案

不要使用智能键。

strong>

这是一个不好的主意，称为智能键或在键中编码信息。

请参见此答案，其中包含此摘录：

尽管现在很容易实现智能钥匙，但是很难推荐你创建一个自己不是一个自然钥匙，因为他们往往最终遇到麻烦，无论他们的优势，因为它使得数据库难以重构，施加难以改变的顺序，并且可能不是对于您的查询是最佳的，如果智能键包括非数字字符，则需要字符串比较，并且在帮助范围中不如复合键有效基聚集。它也违反了每个列都应该存储原子值的基本关系准则。

智能键往往会超出原始的编码约束。

（请注意，座位位置通常由智能钥匙标识，因为它们是行号和跨行计数，但它们通常在物理上永久性地用螺栓固定在该地层中。 ）

了解数据库设计。

以最直接的方式描述您的业务。这就是关系模型数据库& DBMS工作。

找到足够的fill-in-the- [named-] blanks句子模板来描述您的业务情况：

 customer [cid] has name [firstname] [lastname] 
 AND customer [cid] has a phone number [phonenumber] of type [type] ... 
客户[cid]可以使用信用卡＃[card_no]
座位[seatid]在第[row]列，第[column]列
预订
座位[seatid]暂时承诺未完成预订
 ... 
  

对于每个这样的参数化句子模板（aka predicate ）都有一个基表，其中空白/参数的名称是列名。表中的每一行表示从其列值填充空白得到的语句（命题）;

然后为每个表找到每个函数依赖性 （FD）。 （当谓词可以以... AND 列 = F（ column1 ，...）的形式表示时， column1 ，...} 功能上确定 列，候选键（CK）。 （ CK是不包含较小超级密钥的超级密钥。）然后找到每个连接依赖性（JD）。 （一些谓词对于某些数目的AND和...说... AND ...。当每个谓词...的表看起来像你从中获取的时候，有一个JD

然后将表格标准化为 em>第五范式（5NF）。这意味着分解（即，替换其谓词是...的表替换其中JD... AND ...所持有的表），直到每个拥有的JD是由CKs隐含的（即，当来自CK的FD的JD来自成立时，必须保持）。（出于性能的原因，也可以通过组合来定义

请参阅此答案

a>和这一个。

然后我们通过描述我们想要的行。我们通过连接基表谓词和逻辑运算符（即AND，OR，NOT，FOR SOME，FOR ALL等）和函数调用来给出我们想要的表的谓词，和/或通过关系运算符连接基表名即JOIN，UNION，MINUS / EXCEPT，PROJECT / SELECT，RENAME / AS），以给出我们想要的表和/或两者的值（例如RESTRICT / WHERE）。

两个表的JOIN保存从中创建真正语句的行，即具有谓词，它们的谓词的AND;和UNION的OR，MINUS / EXCEPT和AND NOT;并且表的PROJECT / SELECT 列在其谓词之前插入FOR SOME 所有其他列;和RESTRICT / WHERE在其谓词之后放置AND 条件;并且列的RENAME / AS重命名其谓词中的该参数。因此，表表达式对应于谓词：表（基表或查询结果）值包含从其（基表或查询表达式）谓词创建true语句的行。

请参阅此答案。

同样适用于

p>请参阅此答案。

This is a mini-project of mine - Airline reservation system - lets call this airline FlyMi : I have a database (Not decided which one, friend of mine wants to go with MongoDB).

Anyhoo, this is my requirement : I have a table which has details of the flight - Flight number, schedule etc. I'm going to use this table to perform various operations - booking , cancellation , modification

This is where I'm stuck : For the desktop app and the web application - I'm offering an option to select seats. This means I've got to keep track of which seats are booked , which ones are not. And assume I have an UI , which shows seats as
Red - Booked
Green - Not Booked.

And all of this - for each and every flight. My question is : What do you think would be the most efficient way to track seat bookings , for each flight in that airline?

Current Idea : Keep a table named passenger - with all the details such as name , address etc. which keep track of all passengers, and maintain a passenger ID such that , first 4 characters are flight ID, Last 2 character are seat numbers they have chosen, with random number in-between ( I say random because I think it is immaterial here). So, for any flight , If I have to find out number of un-booked seats, I will have to scan through every passenger , who has booked, and who has booked in that flight. I think this is really in-efficient. Provide me with the most efficient logic to do this.

解决方案

Don't use "smart keys".

This is a bad idea called "smart keys" or "encoding information in keys".

See this answer which contains this excerpt:

Despite it now being easy to implement a Smart Key, it is hard to recommend that you create one of your own that isn't a natural key, because they tend to eventually run into trouble, whatever their advantages, because it makes the databases harder to refactor, imposes an order which is difficult to change and may not be optimal for your queries, requires a string comparison if the Smart Key includes non-numeric characters, and is less effective than a composite key in helping range-based aggregations. It also violates the basic relational guideline that every column should store atomic values

Smart Keys also tend to outgrow their original coding constraints

(Notice that seat locations are typically identified by smart keys in that they are a row number and a count across a row. But they are also typically visibly physically permanently bolted into that formation. And imagine if they were labelled and rearranged.)

Educate yourself about database design.

Just describe your business in the most straightforward terms. That is how relational model databases & DBMSs work.

Find enough fill-in-the-[named-]blanks sentence templates to describe your business situations:

"customer [cid] has name [firstname] [lastname]
    AND customer [cid] has a phone number [phonenumber] of type [type] ..."
"customer [cid] can use credit card #[card_no]"
"seat [seatid] is at row [row] and column [column]"
"seat [seatid] is booked"
"seat [seatid] is temporarily committed to an unfinished booking"
...

For each such parameterized sentence template (aka predicate) have a base table where the names of the blanks/parameters are column names. Each row in a table states the statement (proposition) got from filling in the blanks per its column values; each row not in a table states NOT the statement from filling in the blanks per its column values.

Then for each table find every functional dependency (FD) that holds. (When a predicate can be expressed in the form "... AND column = F(column1,...)" then we say that column set {column1,...} functionally determines column column and that FD set → column holds.) Then identify every candidate key (CK). (A superkey is a column set that functionally determines every column. Ie that is unique, ie where each subrow of values for those columns appears only in one row of a table. A CK is a superkey that doesn't contain a smaller superkey.) Then find every join dependency (JD). (Some predicates say "... AND ..." for some number of ANDs & "..."s. There is a JD when the table for each predicate "..." would look like what you get from taking only its columns from the original table.) Note that every FD comes with an associated (binary) JD.

Then normalize your tables to fifth normal form (5NF). This means decomposing (ie replacing a table in which JD "... AND ..." holds by tables whose predicates are the "..."s) until each JD that holds is implied by the CKs (ie must hold when the JDs from the FDs from the CKs hold.) (For performance reasons one can also then denormalize by combining to base tables that aren't in 5NF.)

See this answer and this one.

Then we query by describing the rows we want. We do this by connecting base table predicates with logical operators (ie AND, OR, NOT, FOR SOME, FOR ALL etc) and function calls to give the predicates for the tables we want and/or by connecting base table names by relation operators (ie JOIN, UNION, MINUS/EXCEPT, PROJECT/SELECT, RENAME/AS) to give the values of the tables we want and/or both (eg RESTRICT/WHERE).

The JOIN of two tables holds the rows that make a true statement from, ie has as predicate, the AND of their predicates; and the UNION the OR, the MINUS/EXCEPT the AND NOT; and that PROJECT/SELECT columns of a table puts FOR SOME all-other-columns before its predicate; and RESTRICT/WHERE puts AND condition after its predicate; and the RENAME/AS of column renames that parameter in its predicate. So a table expression corresponds to a predicate: A table (base table or query result) value contains the rows that make a true statement from its (base table's or query expression's) predicate.

See this answer.

The same goes for constraints, which are true statements that collectively describe the application situations and database states than can arise given the situations that can arise and the base table predicates.

See this answer.

这篇关于需要帮助开发DB逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！