CODEWITHSUNDEEP
sqloracle-databasemetadatacreate-table
franvergara66
franvergara66

Reputation: 10784

Creating a table in SQL, and taking advantage of metadata

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.

Upvotes: 1

Views: 260

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

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.

  • It makes updates terribly difficult. If you decide that Mexico belongs with the Central American region rather than the North American region, for example, you would have to go through and update all the primary keys for your Mexican stores along with every place in the system that those primary keys are referenced. That is a tremendous amount of work, it is incredibly unlikely to actually get done, and it is a very costly operation in terms of runtime and testing.
  • It makes querying the data much more complicated. Now if you want to find all the stores in Mexico, you have to write code that makes multiple calls to SUBSTR and INSTR to 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.
  • And it violates general principles of normalization. City, country, and region are separate identifiable attributes of a store. They should be given a separate column.

Upvotes: 8

Related Questions