需要有关存储密码系统(邮政编码)和运输公司服务详情的建议 [英] Need advice on storing pincode system (Zip code) and Shipping company Service Details

问题描述

我正在创建系统，该系统可以根据电子商务平台的给定密码(邮政编码)识别运输公司(Fedex、UPS 等).

I'm on the process of creating system that could identify shipping companies (Fedex,UPS etc...) from a given pincode (zip code) for e-commerce platform.

运输公司将提供他们提供取件和送货服务的密码列表.因此，当我们收到带有卖家密码和买家密码的产品订单时，我需要获取提供这两种服务(即取货和送货)的运输公司的列表.

The shipping companies will provide a list of pincodes where they provide pickup and delivery. So when we get an order for a product, with the pincode of the seller and with pincode of buyer i need to get a list of shipping companies who provides both these services ie pickup and delivery.

我最初的计划是创建一个类似下面的表结构；

My initial plan is to create a table structure like following one;

shipping_companies
-------------------
1. Fedex
2. UPS
3. DHL

pincodes
----------
id pincode | shipping_ids_pickup | shipping_ids_delivery
1. 263152           1,2                2,3

pincodes 表将包含 pincode，并且有两列用于取货，另一列用于交付.

The pincodes table will have the pincodes and will have two columns one for pickup and another for delivery.

因此，当用户购买产品时，我需要找到从卖家位置取货并在买家位置送货的运输公司.

So when a user purchases a product i need to find a shipping company that has pickup from the sellers location and also has delivery at buyers location.

如果我们将来要添加另一家运输公司，我可以通过附加 id 来更新 shipping_ids_pickup、shipping_ids_delivery 的密码.

If we are adding another shipping company in the future i can update the shipping_ids_pickup, shipping_ids_delivery for a pincode by appending the id.

有人可以提出更好的解决方案吗?目前的方法有什么问题吗?

Can someone suggest a better solution. Is there any issue with current approach?

推荐答案

目前的方法有什么问题吗?

Is there any issue with current approach.

是的.您将多个值存储在单个列中:

Yes. You're storing multiple values in single columns:

pincodes
----------
id pincode | shipping_ids_pickup | shipping_ids_delivery
1. 263152           1,2                2,3

                     ^------ here ------^

这将使查询和维护数据变得比实际需要的更加困难.该代码将更多地关注字符串操作，而不是将客户与运输公司匹配.

This is going to make querying and maintaining the data much more difficult than it needs to be. The code is going to be putting more focus on string manipulation than on matching customers to shipping companies.

业务需求是将客户与运输公司相匹配.业务需求不是操纵字符串值.

The business need is to match customers with shipping companies. The business need is not to manipulate string values.

相反，将单个数据元素存储为...单个数据元素.从您拥有的两个实体开始:

Instead, store individual data elements as just that... individual data elements. Start with the two entities that you have:

Shipping Companies
--------
ID (int, PK)
Name (string)

Pincodes
--------
ID (int, PK)
Pincode (string)

这些实体具有多对多关系.所以创建一个表来链接它们:

These entities have a many-to-many relationship. So create a table to link them:

Shipping Company Pincodes
--------
ID (int, PK)
Shipping Company ID (int, FK)
Pincode ID (int, FK)
Pickup (bit)
Delivery (bit)

关系本身本质上变成了领域中的一个实体.不一定是主要业务实体(即，不是聚合根)，而是一个逻辑实体.该实体与其相关的聚合根分开维护.

The relationship itself essentially becomes an entity in the domain. Not necessarily a primary business entity (that is, not an aggregate root), but a logical entity all the same. That entity is maintained separately from the aggregate roots to which it relates.

查询和更新数据变得很多容易，并且代码可以专注于正在执行的业务逻辑的语义，而不是执行它的各种开销.

Querying and updating the data becomes a lot easier, and the code can focus on the semantics of the business logic being performed rather than all sorts of overhead of performing it.

这篇关于需要有关存储密码系统(邮政编码)和运输公司服务详情的建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！