SQL - 在 SQL 中创建表

发布于 2024-11-19 17:14:54 字数 1051 浏览 3 评论 0原文

如果我走在正确的道路上,请指导我。

我正在尝试为 XMobile Bill 创建数据库架构,以及如何定义 PKFK对于表Bill_Detail_Lines

以下是假设:

  1. 每个客户都有一个唯一的关系编号。
  2. Bill_no 是唯一的,因为它每月生成。
  3. X 每个月都可以拨打同一个手机号码。
  4. Account_no 与每个手机号码相关联,并且不会更改。

架构:

table: Bill_Headers

Relationship_no    - int, NOT NULL , PK
Bill_no            - int, NOT NULL , PK
Bill_date          - varchar(255), NOT NULL
Bill_charges       - int, NOT NULL

table: Bill_Detail_Lines

Account_no    - int, NOT NULL
Bill_no       - int, NOT NULL , FK
Relationship_no - int, NOT NULL, FK
Phone_no      - int, NOT NULL
Total_charges  - int

table: Customers

Relationship_no    - int, NOT NULL, PK
Customer_name      - varchar(255)
Address_line_1     - varchar(255) 
Address_line_2     - varchar(255) 
Address_line_3     - varchar(255) 
City               - varchar(255)
State              - varchar(255)
Country            - varchar(255)

Please guide me if I'm on right track.

I'm trying to create database schema for Mobile Bill for a person X and how to define PK, FK for the table Bill_Detail_Lines.

Here are the assumptions:

  1. Every customer will have a unique relationship number.
  2. Bill_no will be unique as it is generated every month.
  3. X can call to the same mobile no every month.
  4. Account_no is associated with every mobile no and it doesn't change.

Schema:

table: Bill_Headers

Relationship_no    - int, NOT NULL , PK
Bill_no            - int, NOT NULL , PK
Bill_date          - varchar(255), NOT NULL
Bill_charges       - int, NOT NULL

table: Bill_Detail_Lines

Account_no    - int, NOT NULL
Bill_no       - int, NOT NULL , FK
Relationship_no - int, NOT NULL, FK
Phone_no      - int, NOT NULL
Total_charges  - int

table: Customers

Relationship_no    - int, NOT NULL, PK
Customer_name      - varchar(255)
Address_line_1     - varchar(255) 
Address_line_2     - varchar(255) 
Address_line_3     - varchar(255) 
City               - varchar(255)
State              - varchar(255)
Country            - varchar(255)

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

江南烟雨〆相思醉 2024-11-26 17:14:54

我建议为 Bill_Detail_Lines 设置一个主键。如果每行代表对给定号码进行的所有呼叫的总数,则自然 PK 似乎是 (Relationship_no, Bill_no, Phone_no),或者可能 (Relationship_no, Bill_no, Account_no)< /代码>。

如果每行代表一个调用,那么我可能会添加一个 Line_no 列并进行 PK (Relationship_no, Bill_no, Line_no)

I would recommend having a primary key for Bill_Detail_Lines. If each line represents a total of all calls made to a given number, then the natural PK seems to be (Relationship_no, Bill_no, Phone_no), or maybe (Relationship_no, Bill_no, Account_no).

If each line instead represents a single call, then I would probably add a Line_no column and make the PK (Relationship_no, Bill_no, Line_no).

半边脸i 2024-11-26 17:14:54

是的,对于我来说,一切看起来都很好。

Yes, as for me, everything looks good.

风筝在阴天搁浅。 2024-11-26 17:14:54

我不得不不同意,有几个“标准”没有得到遵循。是的,设计看起来不错,但命名约定不合适。

首先,表名应该是单数(很多人不同意这一点)。

如果表上有一个 int PK,标准是将其称为“ID”,因此您有“SELECT Customer.ID FROM Customer” - 例如。然后,您还可以完全限定 FK 列,例如:Bill_Headers 上的 CustomerID 而不是Relationship_no,然后您必须在表定义中检查它以记住它的相关内容。

我还始终牢记的一点是,使列标题尽可能清晰和简短,而不混淆名称。例如,Bill_Headers 上的“Bill_charges”可能只是“费用”,因为您已经在 Bill_Header 上(<-该死的'),日期也是如此,但日期可能更具描述性、CreatedDate、LastUpdatedDate 等...

最后,请注意对多个列进行硬编码,其中一个列就足够了,反之亦然。具体来说,我正在谈论:

Address_line_1 - varchar(255)
地址行_2 - varchar(255)
Address_line_3 - varchar(255)

这会导致稍后的头痛。 SQL 确实能够在字符串中存储换行符,因此将它们组合成一个“Address - varchar(8000)”将是最简单的。理想情况下,这将位于一个单独的表中,将其称为 Customer_Address,其中包含 int“CustomerID - int PK FK”列,您可以在其中输入特定信息。

请记住,这些只是建议,因为没有任何一种数据库设计方法是每个人都应该遵循的。这些是最佳实践,最终由您做出决定。

I have to disagree, there's a couple of 'standards' which aren't being followed. Yes the design looks ok, but the naming convention isn't appropriate.

Firstly, table names should be singular (many people will disagree with this).

If you have a single int, PK on a table, the standard is to call it 'ID', thus you have "SELECT Customer.ID FROM Customer" - for instance. You also then fully qualify the FK columns, for instance: CustomerID on Bill_Headers instead of Relationship_no which you then have to check in the table definition to remember what it's related to.

Something I also always keep in mind, is to make the column header as clear and short as possible without obfuscating the name. For instance, "Bill_charges" on Bill_Headers could just be "Charges", as you're already on the Bill_Header(s) (<- damn that 's'), same goes for Date, but date could be a bit more descriptive, CreatedDate, LastUpdatedDate, etc...

Lastly, beware of hard-coding multiple columns where one would suffice, same other way around. Specifically I'm talking about:

Address_line_1 - varchar(255)
Address_line_2 - varchar(255)
Address_line_3 - varchar(255)

This will lead to headaches later. SQL does have the capability to store new line characters in a string, thus combining them to one "Address - varchar(8000)" would be easiest. Ideally this would be in a separate table, call it Customer_Address with int "CustomerID - int PK FK" column where you can enter specific information.

Remember, these are just suggestions as there's no single way of database design that everyone SHOULD follow. These are best practices, at the end of the day it's your decision to make.

双手揣兜 2024-11-26 17:14:54

有几个错误:

  1. Realtionship_no 和 Bill_no 是 int。确保条目在整数范围内。最好将它们视为 varchar() 或 char()

  2. Bill_date 应采用 Date 数据类型

  3. 在表 Bill_Detail_Lines 中,最好将 Account_no 设为varchar() 或 char() 因为帐号太长。 Phone_no 也是如此。

您的 Customers 表一切正常,只是您将城市、州和国家的 varchar() 大小设置为 255,该值太大了。您也可以使用较小的尺寸。

There are a few mistakes:

  1. Realtionship_no and Bill_no are int. Make sure that the entries are within the range of integer. It is better to take them as varchar() or char()

  2. Bill_date should be in data type Date

  3. In table Bill_Detail_Lines also, it is better to have Account_no as varchar() or char() because of the long account no. And the same goes with Phone_no.

Your Customers table is all fine except that you have taken varchar() size as 255 for City State and Country which is too large. You can work with smaller size also.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文