使用 SQL 创建表并利用元数据
做作业，要求我做一个数据库的表，该表对应于百货商店的一个分店，该表必须包含以下信息：
CREATE TABLE STORE
(
id_store varchar (50),
name_store varchar (50),
city varchar (50),
country varchar (50),
region varchar (50)
);
我的问题是我们需要有城市、国家和地区的字段，还是通过元数据信息，我可以设置一个模式，通过主键 id_state 的分解来提供该信息？例如，前 3 位数字对应于国家/地区，接下来的 3 位数字对应于城市，依此类推。
To homework, asking me to make a table of a DB, the table corresponds to a branch of a department store, this table must contain the following information:
CREATE TABLE STORE
(
id_store varchar (50),
name_store varchar (50),
city varchar (50),
country varchar (50),
region varchar (50)
);
My question is do we need to have the fields of city, country and region, or by the metadata information I can set a pattern for which that information is provided by the decomposition of the primary key id_state? For example the first 3 digits correspond to the country and the next 3 correspond to the city and so on.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您永远不会希望创建一个系统，其中主键尝试对有关行中其他属性的数据进行编码。这是通往巨大心痛和巨大痛苦的道路。
SUBSTR和INSTR来解析一行位于哪个国家/地区。与单独的列相比，索引和优化器理解要困难得多。确保没有“MEX”、“mex”和“mx”行都代表墨西哥也更加困难。You would never want to create a system where the primary key tries to encode data about other attributes in the row. That is the path to great heartache and much suffering.
SUBSTRandINSTRto parse out which country a row is in. That is eminently more difficult to index and for the optimizer to understand than a separate column. It's also much harder to ensure that you don't have 'MEX', 'mex', and 'mx' rows all representing Mexico.