表结构问题 [英] table structure problem
问题描述
帮助:-)我看不出这应该如何布局！
考虑汽车租赁公司的情景。他们有汽车。和批次
（全国范围内）和客户。到目前为止，很容易查询其中的每一个。一个客户租用了一辆
的汽车，所以会说租赁。表。一辆车
是一次又一次租用和退回的，所以每辆
车都会有很多租车。要记住的一件事是，汽车没有分配到特定的地段
所以无论是顾客有车还是很多（某处）和
可供出租。
所以问题是如何布置表格（和查询）以找出
a特定车辆的位置？或者说所有车都在哪里。我不认为这是乍一看这么简单。显然，在car表中可以有一个包含LocationId的
列，但这并不表示
是否是LotId或CustomerId。
LotId和CustomerId可能会有一个列，并且一次只填充一个但是没有
适合优雅的SQL查询吗？ ？
我以为可能有一个LocationIdType字段。它可以设置为
表示LocationId是否是批次。或客户或这允许我
加入批次或客户表，但我想知道是否还有另外的
方式。
是吗足够的信息继续下去？如果它有帮助我可以提供更多细节。
谢谢，
汤姆
在没有
机会详细分析特定业务场景的情况下，很难在线提供可靠的设计建议。考虑到这个
警告并根据给出的信息我建议设计
如下。对于大多数用户而言，客户和批次显然是不同的实体，因此请将它们分开并使用约束来验证数据：
创建表格车辆（vin VARCHAR（10） ）PRIMARY KEY，lotid INTEGER NULL
REFERENCES Lots（lotid），customerid INTEGER NULL REFERENCES Customers
（customerid），CHECK（（lotid IS NOT NULL且customerid IS NULL）或（lotid IS
NULL和customerid IS NOT NULL）/ * Lot或Customer但不是两者都是* /））
如果要检索位置作为一个列，您可以使用
COALESCE：
SELECT COALESCE（lotid，customerid）AS位置
来自车辆
您还可以确保相同的ID不用作批次和客户：
CREATE TABLE VehicleLocations（locationid INTEGER PRIMARY KEY，loctype
CHAR（1）检查（loctype IN（''L''，''C''）），UNIQUE（locationid，loctype））
CREATE TABLE客户（customerid INTEGER PRIMARY KEY，loctype CHAR（1）NOT
NULL CHECK（loctype =''C''），FOREIGN KEY（customerid，loctype）REFERENCES
VehicleLocations（ locationid，loctype））
CREATE TABLE Lots（lotid INTEGER PRIMARY KEY，loctype CHAR（1）NOT NULL CHECK
（loctype =''L'' ），FOREIGN KEY（lotid，loctype）REFERENCES参考文献VehicleLocations
（locationid，loctype））
-
David Portas < br $>
------------
请只回复新闻组
-
" David Portas" < RE **************************** @ acm.org>写道......
在没有详细分析特定业务场景的机会的情况下，很难在线提供可靠的设计建议。
毫无疑问，我曾希望我已经传达了它的要点。所以而不是讨论
代码而不是你基本上建议车辆行包含两个
id'，两个用于两个类型。它可能被发现的地方？这是我考虑过的场景中的一个。
。我不喜欢它（尽管我可能会被困b
）是不是不可能预测到会有一个
三分之一的时间车辆的类型。它可能在维修中心
。会有一个表格（它们与批次不同，或者是b $ b和客户），每个都有一个Id。那时我必须给车辆表添加第三个Id字段。
我绝不会诋毁你的建议或扔东西在
最后时刻我只考虑所有应用程序及时增长和个人增长
我希望有一些增长空间。也就是说，我现在想不出更好的方式。
它可能会运作良好。我当然可以检索所有车辆。
的那些很多都会有一个非null的LotId，客户的那些
有一个非null的CustomerId。可以隔离特定批次或特定客户的所有车辆。
您还可以确保相同的ID不用作批次和客户：
该部分已经处理，我有一个ID服务分配所有的ID。
谢谢，
Tom
也许：
CREATE TABLE车辆（vin VARCHAR（10）PRIMARY KEY，locationid INTEGER NOT
NULL REFERENCES VehicleLocations（locationid））
然后，您可以轻松地在
中添加新的位置实体，例如维修中心，与客户和批次相同。虽然如果您不需要获取有关您可能仍然可以使用
两个表格的位置的大量信息：一个用于客户，一个用于其他位置。
-
David Portas
------------
请仅回复新闻组
-
Help :-) I can''t see how this should be laid out!
Consider the scenario of a car rental company. They have "cars" and "lots"
(nationwide) and "customers." So far it is easy to query each of these. A
car is rented by a customer so there would be say a "rentals" table. A car
is rented and returned over and over so there would be many rentals for each
car. One thing to remember is that the car isn''t assigned to a specific lot
so either the customer has the car or it is in a lot (somewhere) and
available for rental.
So the question is how do I layout the tables (and query) to find out where
a specific car is? Or where all the cars are for that matter. I don''t
think it is as simple as it looks at first glance. Clearly there can be a
column in the car table containing a LocationId but that wouldn''t indicate
whether it is a LotId or a CustomerId. There could be a column for both
LotId and CustomerId and with only one filled in at a time but that doesn''t
lend itself to an elegant SQL query does it?
I thought there might be a LocationIdType field. It could be set to
indicate whether the LocationId is a "lot" or a "customer" which permits me
to join with the lots or customers table but I wondered if there is another
way.
Is that enough information to go on? I can give more details if it helps.
Thanks,
Tom
It''s very difficult to give reliable design advice online without the
opportunity to analyse a particular business scenario in detail. With that
caveat in mind and based on the information given I would suggest a design
as follows. Customers and Lots are clearly different entities for most
purposes so keep them separate and use constraints to validate the data:
CREATE TABLE Vehicles (vin VARCHAR(10) PRIMARY KEY, lotid INTEGER NULL
REFERENCES Lots(lotid), customerid INTEGER NULL REFERENCES Customers
(customerid), CHECK ((lotid IS NOT NULL AND customerid IS NULL) OR (lotid IS
NULL AND customerid IS NOT NULL) /* Lot or Customer but not both */ ))
If you want to retrieve the location as a single column, you can use
COALESCE:
SELECT COALESCE(lotid,customerid) AS location
FROM Vehicles
You can also ensure that the same id is not used as both Lot and Customer:
CREATE TABLE VehicleLocations (locationid INTEGER PRIMARY KEY, loctype
CHAR(1) CHECK (loctype IN (''L'',''C'')), UNIQUE (locationid, loctype))
CREATE TABLE Customers (customerid INTEGER PRIMARY KEY, loctype CHAR(1) NOT
NULL CHECK (loctype = ''C''), FOREIGN KEY (customerid,loctype) REFERENCES
VehicleLocations (locationid,loctype))
CREATE TABLE Lots (lotid INTEGER PRIMARY KEY, loctype CHAR(1) NOT NULL CHECK
(loctype = ''L''), FOREIGN KEY (lotid,loctype) REFERENCES VehicleLocations
(locationid,loctype))
--
David Portas
------------
Please reply only to the newsgroup
--
"David Portas" <RE****************************@acm.org> wrote...
It''s very difficult to give reliable design advice online without the
opportunity to analyse a particular business scenario in detail.
No doubt, I had hoped I had conveyed the gist of it. So rather than discuss
"code" you''re basically suggesting that the vehicle row would contain two
id''s, one each for the two "types" of places it might be found? It is one
of the scenarios I considered. What I don''t like about it (though I may be
stuck) is that it isn''t impossible to predict a time when there would be a
third type of place the vehicle could be. It could be at a repair center
for instance. There would be a table of them (they are different than lots
and customers) and each would have an Id of course. At that point I have to
add a third Id field to the vehicles table.
I am in no way denigrating your suggestion or "throwing something in at the
last moment" I am only considering that all apps grow in time and personally
I like to have some room for growth. That said, I can''t think of a better
way at the moment.
And it might work well. I can retrieve all the vehicles of course. The
ones that are at a lot would have a non-null LotId, the ones at a customer
have a non-null CustomerId. All vehicles at a particular lot or with a
particular customer can be isolated.
You can also ensure that the same id is not used as both Lot and Customer:
That part is handled, I have an ID "service" which assigns all the Ids.
Thanks,
Tom
Maybe:
CREATE TABLE Vehicles (vin VARCHAR(10) PRIMARY KEY, locationid INTEGER NOT
NULL REFERENCES VehicleLocations (locationid))
Then you can easily add new location entities such as Repair Centres in the
same way as for Customers and Lots. Although if you don''t need to capture
much information about the locations you could probably still manage with
two tables: one for Customers and one for other locations.
--
David Portas
------------
Please reply only to the newsgroup
--
这篇关于表结构问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
