在数据库表中定义列表. [英] Defining lists in database tables.

问题描述

大家好，这里是关于数据库设计的快速查询....

我正在建立一个个人网站，其中列出了澳大利亚的农场以及有关农场的详细信息.

我想对其进行设置，这样我不仅可以识别与该农场相关的主要农作物，而且还可以识别出次要农作物的清单.

现在，我已经读了很多书，所以我掌握了一些设计基础知识.我了解主键和外键，以及如何为主要农作物和农场建立和关联表.我似乎无法理解如何设置另一个.

我会使用Guid来识别每种农作物.

我是否只将行分配给nvarchar(max)，然后添加一个以逗号分隔的字符串以及作物向导的列表?

任何输入将是最欢迎的.

干杯

Hi folks, A quick query on database design here....

I''m building a personal site which lists farms in Australia with details about the farm.

I''d like to set it up so that I can not only identify a primary crop associated with that farm, but that a list of secondary crops can be identified also.

Now I''ve read a fair bit so I have the basics of design down (sort of). I know about primary and foreign keys and how I would go about setting up and associating the tables for primary crop and the farm. I just can''t seem to get my head round how I would set up the other.

I would be using Guids to identify each crop.

Do I just assign the row to the nvarchar(max)then add a comma-delimited string with a list of the crop Guids?

Any input would be most welcome.

Cheers

推荐答案

您将需要两个表来执行此操作.

第一张桌子:
FarmID，PrimaryCropGuid，其他字段.
FarmID是主键.

第二张表:
FarmID，SecondargCropGuid.
FarmID是指向第一个表中FarmID的外键.

现在，如果要获取给定农场的所有次生作物，则可以执行以下操作:

You will need two tables to do this.

First table:
FarmID, PrimaryCropGuid, other fields.
FarmID is the primary key.

Second table:
FarmID, SecondargCropGuid.
FarmID is the foreign key pointing to the FarmID in first table.

Now if you want all secondary crops of a given farm then you can do:

SELECT SecondargCropGuid from SecondTable WHERE FarmID='GivenID';

您还可以使用以下方法找到所有种植次生作物的农场:

You can also find all farms which grow secondary crops with:

SELECT FarmID from SecondTable WHERE SecondaryCropGuid='GivenGuiD';

-Saurabh

-Saurabh

这篇关于在数据库表中定义列表.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！