Is there a way to add an attribute to only 1 row in SQL?

Take this table as an example :

CREATE TABLE UserServices (
    ID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Service1 TEXT,
    Service2 TEXT,
    .
    .
    . 
) ENGINE = MYISAM;

Every user will have different number of services, so lets say the table starts with 10 columns for services for each user. If one user will have 11 services, must all other users have 11 columns also? Now of course it is a table and row needs to have the same number of columns, but it is just seems like an awful waste of memory. Maybe the use of another database type is better?

Thank you!!

Answer

Storing a boatload of nulls isn’t really a “waste of memory” because the space is negligible – hard disks cost pence per gigabyte, programmers cost tens/hundreds of $/hr so it’s certainly economical to burn the space and it’s not really a great argument for avoidance.

There is a better argument though, as others have said; databases don’t do variable numbers of columns for a particular ID in a table, but they DO do variable numbers of rows per ID.. This is how DBs are designed: columns are fixed, rows are variable. Everything that a database does and offers in terms of querying, storage, retrieval, internal design etc is optimised towards this pattern

There are well established operations (called pivots) that will turn your vertical arrangement of data into horizontal (with nulls) at query time, so you don’t have to store the data horizontally

Here’s a pivot example:

Table:
ID, ServiceIdentifier, ServiceOwner
1, SV1, John
1, SV2, Sarah
2, SV1, Phil
2, SV2, John
2, SV3, Joe
3, SV2, Mark

SELECT
  ID,
  MAX(CASE WHEN ServiceIdentifier = 'SV1' THEN ServiceOwner END) as SV1_Owner,
  MAX(CASE WHEN ServiceIdentifier = 'SV2' THEN ServiceOwner END) as SV2_Owner,
  MAX(CASE WHEN ServiceIdentifier = 'SV3' THEN ServiceOwner END) as SV3_Owner
FROM
  Table
GROUP BY
  ID


Result:
ID SV1_Owner SV2_Owner SV3_Owner
1  John      Sarah
2  Phil      John      Joe
3            Mark

As noted, it’s not a huge cost to just store the data horizontally and if you’re sure the table will never change/ not need new columns adding on a weekly basis to cope with new services etc, then it might be a sensible developer optimisation to just have columns full of nulls. If you’ll add columns regularly, or one day have thousands of services, then vertical storage is going to have to be the way it goes

Leave a Reply

Your email address will not be published. Required fields are marked *