简单的SQL数据建模问题
鉴于我有一个保存车辆信息的表，其中一条信息是 VehicleType（通常为 6-20 个字符），那么最好设计这样的表的技术原因是什么：
Vehicles
VehicleID
VehicleTypeID (INT)（与 VehicleTypes 表中的 INT 相关）
与此：
Vehicles
VehicleID
VehicleType (NVARCHAR(50))
我能想到一些...
1) 如果车辆类型的描述发生变化，只需在一条记录中进行更改。
2）存储 INT 比存储 NVARCHAR 占用的空间更少（当然，这取决于字符串长度，尤其是如果我更改为 TINYINT。）
几个问题...
1）有索引考虑吗？我假设如果我要在 VehicleType 上建立索引，如果我使用 INT 而不是 NVARCHAR，索引会更快并且占用的空间更少。
2）有任何查询优化问题吗？我知道前一种方法需要 JOIN，但我不认为这会对 SQL 2008 造成负担。
我将捍卫我的立场并希望获得尽可能多的信息。
感谢您抽出时间回复。
谢谢，
达尔维斯
Given that I have a table that holds vehicle information and one of those pieces of information is VehicleType (usually 6-20 characters), what are the technical reasons why it is better to design the tables like this:
Vehicles
VehicleID
VehicleTypeID (INT) (relates to an INT in the VehicleTypes table)
versus this:
Vehicles
VehicleID
VehicleType (NVARCHAR(50))
I can think of a few...
1) If the description of the Vehicle Type changes, it only has to be changed in one record.
2) It takes less space to store an INT than a NVARCHAR (depending, of course, on the string length and especially if I change to TINYINT.)
A few questions...
1) Any indexing considerations? I'm assuming if I'm going to be indexing on the VehicleType, it will be faster and take less space if I'm using INTs rather than NVARCHARs.
2) Any query optimization issues? I know the former method requires a JOIN, but I don't expect that to be taxing to SQL 2008.
I'm going to be defending my position and want to have as much information as possible.
Thanks for taking the time to respond.
Thanks,
Darvis
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
如果您有一个“批准的”车辆类型列表供用户从“VehicleType”表中驱动的车辆类型中进行选择，那么在数据输入表单中也是有益的。如果不这样做，您最终会遇到拼写错误和不在列表中的车辆类型。另外，当添加新的车辆类型时，如果您从查询填充下拉列表，则无需更改数据输入前端，查询将仅获取表中的所有车辆类型。
It also is beneficial in the data entry forms if you have a list of "approved" vehicle types for the users to select from that is driven from the "VehicleType" table. If you don't you'll end up with misspellings and vehicle types that aren't on the list. Plus when new vehicle types are added you don't have to change the data entry front end if you are populating a drop down list from a query, the query will just get all the vehicle types in the table.
前者是 3NF，即正确标准化的数据。
不会自动为外键创建索引。在外键上创建索引是有意义的 - 它很可能被用作标准，但应该考虑数据和对其的访问。 MySQL 对分配索引的空间量有限制（据我所知，没有其他人这样做），虽然索引有助于数据检索，但它们也会影响 INSERT/UPDATE/DELETE 语句。如果处理 SQL Server，我强烈建议阅读金·特里普的《引爆点》系列。
与子查询相比，连接是数据检索和操作的最优选方法...
The former is 3NF, properly normalized data.
Indexes are not automatically created for foreign keys. Index creation on a foreign key makes sense - it's a very likely to be used as criteria, but the data and access to it should be considered. MySQL has a limit on the amount of space for allocating indexes (no others do that I'm aware of), and while indexes help data retrieval, they also impact INSERT/UPDATE/DELETE statements. If dealing with SQL Server, I highly recommend reading Kim Tripp's The Tipping Point series.
A join is the most preferred means of data retrieval and manipulation, vs say a subquery...
请记住，nvarchar 每个字符需要 2 个字节，因此如果 int 是 4 个字符，那么要使用相同的空间，您只能在 nvarchar 列中使用 2 个字符。
如果tinyint（最多255）不够，我会使用int甚至smallint（最多32,767和2字节存储）
所以我会在这种情况下使用第一个表，
不知道您的查询会是什么样子，但可能会考虑切换列并从 typeid 开始
remember that nvarchar requires 2 bytes per character so if an int is 4 character then to use the same space you could only use 2 characters in the nvarchar column.
I would go with int or even smallint (up to 32,767 and 2 bytes of storage) if tinyint (up to 255) is not enough
So I would use the first table in this case
don't know what your queries will look like but might be an idea of switching the columns around and starting with typeid
正确的。还有目前未使用的“车辆类型”，例如燃料电池汽车。
这些是“数据修改异常”
其他人已经回答了索引问题...
Correct. And also what about currently unused "vehicle types" eg fuel cell car.
These are "data modification anomalies"
Other folk have answered the index questions...
基本上，您想要做的就是对表设计进行非规范化。当涉及到报告目的时（当有数百万条记录时），这本质上是有用的，但出于应用程序目的，我会使用一个规范化的数据库，并在其上构建适当的索引。这也有助于引用完整性。
希望这有帮助。
Basically what you are trying to do is to De-normalize the table design. This is essentially useful when it comes to reporting purpose (when there are millions and millions of records) but for application purposes I would go with a normalized database with proper indexes built on them. This also aids in Referential integrity.
Hope this helps.
请小心更改“选择列表”（域）中的名称，例如
车辆类型。如果您使用外键，那么该类型的所有现有条目都将受到影响 - 这有效吗？
我不知道
Vehicle Type是什么意思，但是如果Vehicle Type = Vehicle，例如，如果 Datsun 将其名称更改为 Nissan，则可能会出现
Make问题 - 表中的现有车辆仍然是 Datsun< /em>.....Be careful changing names in 'pick lists' (domains) such as
Vehicle Type.If you are using a foreign key then all existing entries for that type will be impacted - would this be valid?
I don't know what
Vehicle Typeis meant to be, however ifVehicle Type = Vehicle,Makeproblems can occur if, for instance, Datsun changes its name to Nissan - the existing vehicles in the table are still Datsuns.....