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

问题描述

我正在创建一个系统，该系统可以从给定的电子商务平台PIN码(邮政编码)中识别货运公司(联邦快递，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来更新PIN码的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屋！