我应该将美国州存储为数组还是创建表列？

I have an app that houses product data via a Product model and table. Each product has specific state availability (multiple states) that I will need to filter and/or search by in the future. I am hoping to find someone who can tell me the most efficient way to store this data. As I see it, I have two options.

我有一个应用程序,通过产品型号和表格存储产品数据。每个产品都有特定的状态可用性(多个状态),我将来需要过滤和/或搜索。我希望找到能告诉我存储这些数据的最有效方法的人。在我看来,我有两个选择。

The first is to simply create 50 columns in my table, titled with each state name and containing a boolean value. I can then simply filter by = "avail in California" if product.ca. While this certainly works, it seems a bit cumbersome, especially when searching for multiple state availability.

第一种是在我的表中简单地创建50列,标题为每个州名并包含一个布尔值。如果是product.ca,我可以简单地过滤=“在加利福尼亚州使用”。虽然这确实有效,但看起来有点麻烦,特别是在搜索多个状态可用性时。

The second option would be to simply have one column("states") that stores an array of available states and then filter by = "avail in California" if product.states.include? "CA". This seems like a better solution for two reasons. The first, it just allows for a cleaner DB table. Second, and more important, I can allow my user to search by simply saving the user's input as a variable(user_input) and then = "avail in California" if product.states.include? user_input. This solution does call for a little more work up front however when saving the product in the DB, since I won't be able to simply check off a boolean value.

第二个选项是简单地有一个列(“状态”)存储一系列可用状态,然后如果product.states.include则过滤=“加利福尼亚州”。 “CA”。出于两个原因,这似乎是更好的解决方案。第一,它只允许更清洁的DB表。其次,更重要的是,如果product.states.include,我可以通过简单地将用户的输入保存为变量(user_input)然后=“在加利福尼亚州使用”来允许我的用户进行搜索? USER_INPUT。这个解决方案确实需要更多的工作,但是在DB中保存产品时,因为我不能简单地检查一个布尔值。

I think option two makes the most sense, but am hoping for some advice as to why or why not. I have found a few similar questions, but they do not seem to explain which solution would be better, just how to accomplish each.

我认为备选方案2最有意义,但我希望就为什么或为什么不提供一些建议。我发现了一些类似的问题,但它们似乎并没有解释哪种解决方案会更好,只是如何实现每种解决方案。

What should I do?

我该怎么办?

2 个解决方案

#1

You should normalize unless you have a really good reason not to, and I don't see one in your overview.

你应该规范化,除非你有充分的理由不这样做,我在你的概述中没有看到一个。

To normalize, you should have the following tables:

要规范化,您应该具有以下表格:

product table, one record per product

产品表,每个产品一个记录

state table, one record per state

州表,每州一条记录

product_state table, one entry for every product that is in a state

product_state表,每个州的产品的一个条目

The product_state schema looks like this:

product_state架构如下所示:

(product_state_id PK, product_id FK, state_id FK)
UNIQUE INDEX(product_id,state_id);

This allows you to have a product in zero or more states.

这允许您将产品置于零个或多个状态。

#2

I assume that since you’re selling products, you will be charging taxes. There are different taxes by state, county, city. There are country taxes in some countries too.

我认为,既然你在销售产品,你就会收税。州,县,市有不同的税。一些国家也有国税。

So you need to abstract these entities into a common parent, usually called GeopoliticalArea, so that you can point a single foreign key (from, say, a tax rates table) at any subtype.

因此,您需要将这些实体抽象为通用父级(通常称为GeopoliticalArea),以便您可以在任何子类型中指向单个外键(例如,来自税率表)。

create table geopolitical_area (
  id bigint primary key,
  type text not null
);

create table country (
  id bigint primary key references geopolitical_area(id),
  name text not null unique
);

-- represents states/provinces:
create table region (
  id bigint primary key references geopolitical_area(id),
  name text not null,
  country_id bigint references country(id),
  unique (name, country_id)
);

insert into geopolitical_area values 
(1, 'Country'),
(2, 'Region');

insert into country values 
(1, 'United States of America');

insert into region values 
(2, 'Alabama', 1);

#1