SQL Naming Column

Question

I'm creating a table for my school project and in one of those tables, I'm storing different specs about tablets and when it comes to defining the CPU and the RAM of these devices you also need to specify the measuring unit. So can I put a column name for let's say the CPU something like CPU_Ghz ???? or RAM_GB???

I say this because I'll probably want to perform some queries on those values, and I know I can perform special queries to extract just the number in the case that I save that information like this format '4432mah'(which i think is not very good), but since I'm doing the database from scratch is probably saving it as I mentioned above?? CPU_Ghz ???? or RAM_GB??? Battery_mAh??

Appreciate your help Have a great day!

Answer 1

Your suggestion of including units in the column name is good. Too often you see code where a numeric value is held without any context, so it's unclear whether a disk is 1GB (1024MB), 1TB (1024 GB), or something else.

Better is @juergen_d's answer where you hold this information in a softcoded form, as not only does this tell you what the numeric value actually means, but it gives you the option to change this at runtime / vary to the more appropriate units (e.g. if you're storing disk space in GB and you get a new requirement to track your Raspberry Pi which has 1MB memory, it may be stored as 0GB in the old column (if it's an integer value), which is meaningless.

I'd make a couple of amendments to the unit_table approach though.

• First, I'd have a unit table for each type of unit (i.e. one for capacity units such as KB, MB, GB, TB, whilst having a separate table for calculation speed units (MHz, GHz, etc). That way you avoid any risk of having something nonsensical, such as 32GHz hard disk space.

• Second, I'd include a conversion factor. i.e. Include data in the table to allow you to convert to a standard base unit, so that your logic can compare the 1024MB value to the 1GB value and determine that they represent the same underlying value. Should you decide to change your base unit, you have that option.

e.g.

create table CapacityUnit
(
    UnitId nvarchar(12) primary key clustered --e.g. GB
    , Name nvarchar(36) not null -- optional long form; e.g. Gigabyte
    , KbMultiplier bigint not null --i.e. if we store a value of 1GB, what should we multiply by to convert that to KB (or whatever our base unit is)
)
insert CapacityUnit(UnitId, Name, BaseMuliplier)
values ('KB', 'Kilobyte', 1)
, ('MB', 'Megabyte', 1024)
, ('GB', 'Gigabyte', 1024*2014)
, ('TB', 'Terabyte', 1024*2014*2014)
create table ConfigurationSettings
(
    OneRecordOnly bit not null default(1) check(OneRecordOnly=1) primary key
    , ConfigurationSettingsnvarchar(12) not null foreign key references CapacityUnit(UnitId)
)
insert ConfigurationSettings(ConfigurationSettings) values ('KB')

You can then create a calculated column or view over your data which converts this data to the KB value; so that the value's easy to query at runtime / you can easily compare values of different units.

You can also use this approach the other way around; where you use the same units table to provide conversion information, but store all values in their common base type (i.e. per Zohar's answer, but with the benefit of having the data required to perform conversions when required).

Which approach makes more sense depends on how you'll use the data; if you're performing lots of operations where you need to compare or aggregate different records then Zohar's wins; if you're just creating an inventory management system (i.e. where you just want to record and lookup individual records), then having different units per record is less of a burden / it may be more beneficial to store the values as they'd show on the item's specs. Which design to take depends on what you'll do with the data, and how your application logic will handle it (e.g. if you're going to convert everything to a common base for display purposes anyway that may negate the benefit of keeping the original values).

One other consideration not mentioned in your question... Don't forget that as well as the speed of a processor you can have a different number of processors (sockets), and a different number of cores. Thankfully you don't see (or I've never heard of) cases where there may be different sockets with different numbers of cores / different speeds in the same system; so you can likely just hold that information in additional fields, rather than having to have a child table to store each logical processor associated with the device.

Answer 2

Yes, you better include the unit in the column name and not in the data. This way you can store the data as a number and this has many advantages over storing it as a string representing value and unit:

1. You store only one value per column, this satisfy the 1NF rule.
2. The database can enforce values correctness. You can only store the correct type of numbers and within the correct range.
3. All queries are far simpler.
4. You can tell, just by looking at the column name, the measuring unit.
5. You are telling the database users that the value of the column is a specific unit - If you would have stored a string, frequency could have been stored as 1Ghz or 1000Khz and you would have to analyze the units to get the correct value.
6. You can use aggregating functions like SUM, AVG etc' (Thanks Damien!)

Answer 3

You could use a separate units table

units table
-----------
id
unit_name

and then in your devices table refer to it

devices table
-------------
id
device_name
cpu_speed
cpu_unit_id