How to store different names of duplicate columns in MS SQL server 2014?

Question

How can I merge/store the common names in a single row?

I'm trying to set up a satellite database. In my current MS SQL 2014 database the satellites have multiple common names.

I was thinking of a comma seperated list, but after reading this Is storing a delimited list in a database column really that bad? I don't think it's a good idea.

sat_id  off_name    common_name norad_nr    cospar_nr   operator_country
39325   NAVSTAR 66      NAVSTAR 66      37753   2011-036A   US        
39326   NAVSTAR 66      USA 232         37753   2011-036A   US        
39327   NAVSTAR 66      GPS BIIF-2      37753   2011-036A   US        
39328   NAVSTAR 66      GPS 2F-2        37753   2011-036A   US        
39329   NAVSTAR 66      SVN 63          37753   2011-036A   US        
39330   NAVSTAR 66      PRN 01          37753   2011-036A   US        
39331   NAVSTAR 66      SLOT D2         37753   2011-036A   US

how about a main (only one) row for a satellite, and with that `sat PK`, have another table satAKA with (id PK, sat PK value, satName) with all the variants (including main satellite name from main table) — Drew, Jul 15 '15 at 13:30
Thx Drew Pierce, I will follow your advice. I also fixed the title. Do you guys have know of any good books/ebooks/articles on this topic that's related yo my question?(Databasedesign) Thx in advance — Frank, Jul 15 '15 at 13:38
It was a long time ago, but the WROX book on database design was pretty good. — Tab Alleman, Jul 15 '15 at 13:43
@Frank In other words, sat_id #39325-39331 are all the same Satellites, and common_name is just many ways of referring to that same one? — Sam Cohen-Devries, Jul 15 '15 at 14:07

score 0 · Accepted Answer · answered Jul 15 '15 at 14:23

create table satellite
(   satId int auto_increment primary key,
    satName varchar(200) not null
);

create table satAKA
(   id int auto_increment primary key,
    satId int not null,
    akaName varchar(200) not null,
    CONSTRAINT fk_aka_sat
        FOREIGN KEY (satId) 
        REFERENCES satellite(satId)
);

insert satellite (satName) values ('Cisco Teleride Model 7000'),('Norad Spectro Tracker');

-- you know have 2 satellites

insert satAKA(satId,akaName) values (1,'Cisco Teleride Model 7000');
insert satAKA(satId,akaName) values (1,'Cisco T Green Monster');
insert satAKA(satId,akaName) values (1,'Green N. Korean Missile Finder');

insert satAKA(satId,akaName) values (2,'Norad Spectro Tracker');
insert satAKA(satId,akaName) values (2,'Nrd Wyoming Peace');
insert satAKA(satId,akaName) values (2,'Western Overpriced Scrap Metal');

oh look now i screwed up! The mysql tag was removed. Please convert to IDENTITY, etc (FK) accordingly, sorry ! — Drew, Jul 15 '15 at 14:27

How to store different names of duplicate columns in MS SQL server 2014?

1 Answers1