Mysql – What kind of data type to use for numbering in the Harmonized System

datatypesMySQL

The harmonized system uses a numbering that shows the hierarchy between different items. Similar to bookkeeping ledger numbers. Here is an example:

01  LIVE ANIMALS
0103  swine, live
010310  Purebred breeding animals

For each level there are two digits available, and this can go down in my case to up to eight digits in total (so four levels). I would like to catalog the harmonized system codes and their descriptions, but wonder what a good data type is to use in MySQL. These numbers are unique so I would like to make them my Primary Keys as well. Given the hierarchy, I should be able to do some SELECTs that allow me to find out the child records. What I am thinking of is using some sort of INT type, and then doing things like this:

SELECT * FROM accounts WHERE CAST(id AS TEXT) LIKE '01%'

Which should allow me to find the child accounts that fall under the '01' category. The problem here would be to work with the leading zeros. If I use zerofill, then 0103 will become 00000103, so that is not ideal. But if I do not use zerofill then this will be 103. Again not ideal.

Using a text data field instead would solve this, but this feels quite wrong as these are clearly numbers. Alternatively I could make several columns – each for the number group – but this again feels like a weird solution. Any advice is appreciated.

Best Answer

"The harmonized system uses a numbering" - they are not numbers, they are codes. They just happen to use digits instead of Latin letters for the code values.

As you have to retain leading zeros anything numeric will not suit. You have to go with textual types. Suppose the codes were

A01        LIVE ANIMALS
A01B03     swine, live
A01B03C10  Purebred breeding animals

I suspect you would have no concerns about using text? Just think of them this way, but with the A, B and C removed. Or think of your alphabet as consisting of 36 characters, a to z and 0 to 9.

Related Solutions

Mysql – Database Design: What is the best structure for storing site offices and locations

Here is some vanilla SQL:

CREATE TABLE RoomTypes
(
 RoomType VARCHAR(12) NOT NULL,
 UNIQUE (RoomType)
);

CREATE TABLE Zones
(
 Zone VARCHAR(10) NOT NULL,
 UNIQUE (Zone)
);

CREATE TABLE Rooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 UNIQUE (RoomType, Zone, Name), 
 FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
 FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);

CREATE TABLE NumberedRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Numbered'),
 UNIQUE (Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

CREATE TABLE AncillaryRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Ancillary'),
 UNIQUE (Zone, Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.

The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.

Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.

Storing prices in SQLite, what data-type to use

Use an integer & store the prices as the lowest common unit. So, for dollars and cents you'd store it in cents. EG: $1.00 would be stored as 100.

In my experience it's standard practice.

Best Answer

Related Solutions

Mysql – Database Design: What is the best structure for storing site offices and locations

Storing prices in SQLite, what data-type to use

Related Question