我正在尝试为我正在开发的程序设计数据库。在发布我的程序之前,我希望设计是完美无瑕的,因为据说一旦启动就很难更改。总之,我的程序是一个用于购买和销售图书的平台。用户可以在“距离”范围内搜索特定ISBN的书籍。他们可以按价格或日期列出图书。我将在表格后描述程序需要执行的操作。
请记住,ContactInfo表并不是非常重要。我会经常清理它,一旦我将信息从数据库中移出以释放空间。如果这个表导致了严重的效率问题,我完全可以牺牲它,我也不会感到困扰。
BannedUsers(表)...
- userID VARCHAR(50) NOT NULL - banReason VARCHAR(50) NOT NULL
BannedUsers表是我将跟踪禁令的地方。它很少被使用,但如果出于某种原因我想禁用某个人使用我的程序,我只需在其中放入他们的userID。当我的程序启动时,它会检查BannedUsers表,看看用户是否被禁止,如果是,它会给出禁止原因。
我有点困惑该在哪里放置索引(我对它们不太了解)。我只是听说索引可以极大地加快搜索速度。对于我的BannedUsers,我认为在userID列上放置索引是显而易见的?如果是这样,应该使用什么类型的索引?
对于BookListings表,我更加困惑该在哪里放置索引。首先,我将解释我的程序的所有功能(需要执行的操作),以及从我的php脚本中获取的查询:
1. 我使用以下查询将列表输入我的系统。当用户想要出售一本书时,他们“列出”这本书。因此,您可以想象这个操作会经常执行...
我在用户想要购买(搜索)某本书且该书在其附近距离内时,会使用以下查询。就像列出图书的查询一样,这个操作也会被经常使用:
上面的查询是按价格排序的。下面的查询则按日期排序:
以下查询是在列出书籍后立即执行的。这是收集用户联系信息的一种方法:
以下查询语句用于程序开始时查找系统中列出了多少本书(只是为了保持对系统处理的书籍数量的有趣计数,不是非常重要):
以下查询用于查找用户在系统中列出的所有书籍。由于用户需要在删除他们创建的列表之前执行此操作,因此此操作将经常进行。它基本上用于显示所有列表,然后用户选择要删除的列表。
以下是实际删除列表的查询语句:
BookListings (table)...
userID VARCHAR(50) NOT NULL
dateListed timestamp Default: CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
isbn13 VARCHAR(20) NOT NULL
price UNSIGNED TINYINT NOT NULL
email VARCHAR(30) NOT NULL
phone VARCHAR(20) NOT NULL
condition VARCHAR(30) NOT NULL
latitude FLOAT(9,7) NOT NULL
longitude FLOAT(9,7) NOT NULL
ContactInfo (table)...
email VARCHAR(30) NOT NULL
phone VARCHAR(20) NOT NULL
请记住,ContactInfo表并不是非常重要。我会经常清理它,一旦我将信息从数据库中移出以释放空间。如果这个表导致了严重的效率问题,我完全可以牺牲它,我也不会感到困扰。
BannedUsers(表)...
- userID VARCHAR(50) NOT NULL - banReason VARCHAR(50) NOT NULL
BannedUsers表是我将跟踪禁令的地方。它很少被使用,但如果出于某种原因我想禁用某个人使用我的程序,我只需在其中放入他们的userID。当我的程序启动时,它会检查BannedUsers表,看看用户是否被禁止,如果是,它会给出禁止原因。
我有点困惑该在哪里放置索引(我对它们不太了解)。我只是听说索引可以极大地加快搜索速度。对于我的BannedUsers,我认为在userID列上放置索引是显而易见的?如果是这样,应该使用什么类型的索引?
对于BookListings表,我更加困惑该在哪里放置索引。首先,我将解释我的程序的所有功能(需要执行的操作),以及从我的php脚本中获取的查询:
1. 我使用以下查询将列表输入我的系统。当用户想要出售一本书时,他们“列出”这本书。因此,您可以想象这个操作会经常执行...
INSERT INTO Listings
VALUES ('$userID', (NOW() + INTERVAL 2 HOUR), '$isbn13', $price, '$email', '$phone',
'$condition', '$latitude', '$longitude')
我在用户想要购买(搜索)某本书且该书在其附近距离内时,会使用以下查询。就像列出图书的查询一样,这个操作也会被经常使用:
SELECT
*, (
6371 * acos (
cos ( radians($userLatitude) )
* cos( radians( latitude ) )
* cos( radians( longitude ) - radians($userLongitude) )
+ sin ( radians($userLatitude) )
* sin( radians( latitude ) )
)
) AS distance
FROM Listings
WHERE isbn13='$isbn13'
HAVING distance <= $withinDistance
ORDER BY price, dateListed
上面的查询是按价格排序的。下面的查询则按日期排序:
SELECT
*, (
6371 * acos (
cos ( radians($userLatitude) )
* cos( radians( latitude ) )
* cos( radians( longitude ) - radians($userLongitude) )
+ sin ( radians($userLatitude) )
* sin( radians( latitude ) )
)
) AS distance
FROM Listings
WHERE isbn13='$isbn13'
HAVING distance <= $withinDistance
ORDER BY dateListed DESC
以下查询是在列出书籍后立即执行的。这是收集用户联系信息的一种方法:
INSERT INTO ContactInfo
VALUES ('$email', '$phone')
以下查询语句用于程序开始时查找系统中列出了多少本书(只是为了保持对系统处理的书籍数量的有趣计数,不是非常重要):
SELECT COUNT(*) FROM ContactInfo
以下查询用于查找用户在系统中列出的所有书籍。由于用户需要在删除他们创建的列表之前执行此操作,因此此操作将经常进行。它基本上用于显示所有列表,然后用户选择要删除的列表。
SELECT dateListed, isbn13, price
FROM Listings
WHERE userID='$userID'
ORDER BY dateListed DESC
以下是实际删除列表的查询语句:
DELETE FROM Listings WHERE userID='$userID' AND isbn13='$isbn13
请帮助我让我的设计更高效。我不太确定应该在哪里建立索引,因为我知道建立索引会使更新和删除变得更加困难..而我的程序也需要这样做。最初,我考虑对isbn13进行索引(主要搜索对象),但后来意识到我还将搜索纬度和经度,所以我不确定这些是否也需要建立索引...这真的让我很困惑。请告诉我有什么可以改进数据库设计和查询的方法。
Listings(userID)
和Listings(isbn13)
,但是需要一个地理空间专家来就distance
优化给您建议。 - StuartLC