在开发数据库逻辑方面需要帮助 [英] Need help in developing DB logic

问题描述

这是我的一个小项目 - 航空公司预订系统 - 让我们称这家航空公司为 FlyMi :我有一个数据库(不确定哪个，我的朋友想用 MongoDB).

Anyhoo，这是我的要求 :我有一张表，上面有航班的详细信息 - 航班号、时刻表等.我将使用这张表来执行各种操作 - 预订、取消、修改

这就是我卡住的地方:对于桌面应用程序和网络应用程序 - 我提供了一个选择座位的选项.这意味着我必须跟踪哪些座位已预订，哪些未预订.并假设我有一个 UI，它将座位显示为
红色 - 已预订
绿色 - 未预订.

所有这些 - 对于每个航班.我的问题是:对于该航空公司的每个航班，您认为跟踪座位预订的最有效方法是什么?

当前想法:保留一个名为乘客的表格 - 包含跟踪所有乘客的所有详细信息，例如姓名、地址等，并维护一个乘客 ID 以便，首先4 个字符是航班 ID，最后 2 个字符是他们选择的座位号，中间是随机数(我说随机是因为我认为这里无关紧要).因此，对于任何航班，如果我必须找出未预订的座位数量，我将不得不扫描每个乘客，谁已经预订，以及谁已经预订了该航班.我认为这是非常低效的.为我提供执行此操作的最有效逻辑.

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.

推荐答案

不要使用智能键".

这是一个叫做智能钥匙"的坏主意；或在密钥中编码信息".

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

请参阅此答案，其中包含此摘录:

尽管现在很容易实现智能钥匙，但很难建议您创建自己的不是自然钥匙的钥匙，因为无论其优势如何，它们最终都会遇到麻烦，因为它会使数据库更难重构，强加了一个难以改变的顺序，并且可能不是您查询的最佳选择，如果智能键包含非数字字符，则需要进行字符串比较，并且在帮助基于范围方面不如复合键有效聚合.它也违反了每列都应该存储原子值的基本关系准则

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.)

自学数据库设计.

只需用最直接的术语描述您的业务.这就是关系模型数据库和DBMS 有效.

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.

然后为每个表找到每个函数依赖(FD).(当谓词可以用... AND column = F(column1,...)"的形式表示时，我们说列集{column1,...} 功能决定列 column 和 FD 集 → 列持有.)然后识别每个 候选密钥(CK).(superkey 是一个列集，它在功能上决定了每一列.即它是唯一，即这些列的每个子行值仅出现在表的一行中.CK 是一个不包含更小的超键的超键.)然后找到每个连接依赖(JD).(一些谓词说... AND ..."对于一定数量的AND和..."s.当每个谓词..."的表看起来时，有一个JD就像你只从原始表中获取它的列一样.)请注意，每个 FD 都带有一个关联的(二进制)JD.

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.

然后将您的表格标准化到第五范式(5NF).这意味着分解(即用谓词是..."的表替换 JD... AND ..."包含的表)，直到每个 JD 包含由 CKs 暗示(即当来自 CKs 的 FDs 的 JDs 保持时，必须保持.)(出于性能原因，一个人也可以非规范化> 通过组合到不在 5NF 中的基表.)

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.)

参见这个答案和这个.

然后我们通过描述我们想要的行来查询.我们通过将基表谓词与逻辑运算符(即 AND、OR、NOT、FOR SOME、FOR ALL 等)和函数调用连接来实现我们想要的表的谓词和/或通过关系运算符连接基表名称(即 JOIN、UNION、MINUS/EXCEPT、PROJECT/SELECT、RENAME/AS)给出我们想要的表的值和/或两者(例如 RESTRICT/WHERE).

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).

两个表的 JOIN 保存从它们的谓词的 AND 中做出真语句的行，即作为谓词的行；和 UNION OR, MINUS/EXCEPT the AND NOT;并且表的 PROJECT/SELECT columns 将 FOR SOME all-other-columns 放在其谓词之前；并且 RESTRICT/WHERE 将 AND condition 放在其谓词之后；并且 column 的 RENAME/AS 在其谓词中重命名该参数.因此，表表达式对应于谓词:表(基表或查询结果)值包含根据其(基表或查询表达式的)谓词做出真语句的行.

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.

请参阅此答案.

同样适用于约束，它们是真实的语句，它们共同描述了应用程序情况和数据库状态，而不是在给定可能出现的情况和基表谓词的情况下可能出现的情况.

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.

请参阅此答案.

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