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

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Is there a way to add an attribute to only 1 row in SQL? without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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

We are here to answer your question about Is there a way to add an attribute to only 1 row in SQL? - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji