如何设计这个数据库布局？

Question

我正在开发一个类似于 Craigslist 的网站，用户可以在该网站上发帖并在不同的城市销售商品。我的网站和 Craigslist 之间的一个区别是您将能够通过邮政编码进行搜索，而不是在页面上列出所有城市。

我已经有了邮政编码数据库，其中包含每个城市的所有城市、州、纬度、经度和邮政编码信息。好的，现在深入了解我需要做什么以及需要帮助：

1. 虽然我有邮政编码数据库，但它的设置并不适合我的使用。 （我从http://zips.sourceforge.net/免费从互联网下载了它）

2. 我需要帮助设置我的数据库结构（例如我应该使用多少个不同的表以及我应该如何链接它们）。

   我需要帮助设置我的数据库结构（例如

我将使用 PHP 和 MySQL。

到目前为止，我对如何设置数据库的想法是：（虽然我不确定这是否可行。）

场景

有人进入主页，它会告诉他们，“请输入您的邮政编码。”。例如，如果他们输入“17241”，则该邮政编码适用于位于宾夕法尼亚州名为纽维尔的城市。使用当前数据库设置，查询将如下所示：

SELECT city FROM zip_codes WHERE zip = 17241;

查询结果将是“Newville”。我现在在这里看到的问题是，当他们想要在网站的纽维尔部分发布某些内容时，我必须为纽维尔市的帖子设置整个表格。有超过 42,000 个城市，这意味着我必须有超过 42,000 张桌子（每个城市一张），所以必须这样做是疯狂的。

我考虑的一种方法是在邮政编码数据库中添加一列名为“city_id”的列，它是分配给每个城市的唯一编号。例如，纽维尔市的 city_id 为 83。现在，如果有人来并在纽维尔市发布列表，我只需要另一张表。另一张表的设置如下：（

CREATE TABLE postings (
posting_id INT NOT NULL AUTO_INCREMENT,
for_sale LONGTEXT NULL,
for_sale_date DATETIME NULL,
for_sale_city_id INT NULL,
jobs LONGTEXT NULL,
jobs_date DATETIME NULL,
jobs_city_id INT NULL,
PRIMARY KEY(posting_id)
);

for_sale 和 job_column 名称是用户可以在下面列出的帖子类型的类别。会有很多类别不止这两个，但这只是示例。）

因此，现在，当有人访问该网站并且他们正在寻找要购买而不是出售的东西时，他们可以输入他们的邮政编码，例如 17241，这是将运行的查询：（

SELECT city, city_id FROM zip_codes WHERE zip = 17241; //Result: Newville 83

我将使用 PHP 来存储用户在会话和 Cookie 中输入的邮政编码，以便在整个站点中记住它们）

现在它将告诉他们，“请选择您的类别。”。如果他们选择“待售物品”类别，那么这是运行查询并对结果进行排序：

SELECT posting_id, for_sale, for_sale_date FROM postings WHERE for_sale_city_id = $_SESSION['zip_code'];

所以现在我的问题是，这行得通吗？我很确定它会，但我不想设置这个东西并意识到我忽略了一些东西并且必须从头开始。

Answer 1

CREATE TABLE postings ( 
posting_id INT NOT NULL AUTO_INCREMENT, 
for_sale LONGTEXT NULL, 
for_sale_date DATETIME NULL, 
for_sale_city_id INT NULL, 
jobs LONGTEXT NULL, 
jobs_date DATETIME NULL, 
jobs_city_id INT NULL, 
PRIMARY KEY(posting_id) 

这不是您所要求的，但看到这个结构告诉我您需要创建一个相关的表（以及一个可供选择的值的查找表），而不是这样做。如果您在表格中重复列出相同的内容，则需要一个相关的表格。我将有 posts 和 Posting_type （因为您希望每个帖子列出不止一种类型。更像是这种结构。

CREATE TABLE postings ( 
posting_id INT NOT NULL AUTO_INCREMENT, 
posting_description LONGTEXT NULL, 
Posting_date DATETIME NULL, 
PRIMARY KEY(posting_id) 

CREATE TABLE posting_categories ( 
posting_id INT NOT NULL, 
Category_id int
Primary Key (posting_id, Category_id)

CREATE TABLE Categories ( 
category_id INT NOT NULL AUTO_INCREMENT, 
category_description LONGTEXT NULL, 
PRIMARY KEY(category_id) 

这使您可以自由地添加任意数量的类别，而无需更改表结构。

Answer 2

我不会为每种类型的列表都有一组列，而是一个“posting_type”列：

CREATE TABLE postings (
posting_id INT NOT NULL AUTO_INCREMENT,
posting_type char(1),             <<<"J"=job,"S"=for sale, etc.
posting_text LONGTEXT NULL,
posting_date DATETIME NULL,
posting_city_id INT NULL,
PRIMARY KEY(posting_id)

Answer 3

我会考虑像 mongodb 这样的 nosql 解决方案。除了城市-邮政编码关系之外，您还想在此处捕获任何真正的关系约束吗？

看来这里的答案是否定的。

@Konerak 是对的，开始一些事情并从那里开始构建。