CODEWITHSUNDEEP
sqlsql-server
Spufferoo
Spufferoo

Reputation: 127

Normalise/join SQL Server tables

I have inherited a SQL server database which isn't normalised and is giving me headaches. I am not very experienced in SQL and maybe asking stupid questions but would appreciate any advice on how to go forward with the below scenarios.

I have three tables as follows:-

A table of results:

**ResId    CompId     Name     Result**
  1        1          Band A   2
  2        1          Band B   1
  3        1          Band C   3
  4        2          Band A*  2
  5        2          Band B   1
  6        2          Band C   3

A table of Bands current names:

**BandId    BandName**
  1         Band A
  2         Band B
  3         Band C

A table of names the bands were previously known as (linked on BandId):

**oldBandId    BandId    oldBandName**
  1            1         Band A*
  2            1         Band a 
  2            2         Band b

I am looking to consolidate the list of band names in the results table, replacing the band name with a bandId however the result table contains band names from both tables. First question should I create some sort of join table and use this as the bandId in the results table? If so What do I need in this join table, is it just a psuedo-Id of bandId/oldBandId and the table name concatenated then this placed in the results table?

I am then looking to use a query to select all results where the user selects the band by any name variant (new or old) and returns the results including all names linked with the band i.e. choosing Band A would return the results for both Band A and Band A*.

Thanks in advance

Steve

Upvotes: 1

Views: 270

Answers (2)

Roopesh Shenoy
Roopesh Shenoy

Reputation: 3447

I think your current db structure is fine enough - I can't think of any way to improve on it, without complicating it further (especially if you want to retain the old band names).

You can just write a query as so for your need - 

select * from results
where Name = @bandName or
      Name in (select oldBandName 
                from oldBands
                where BandId in (select BandId 
                                from Bands
                                where BandName = @bandName))

Upvotes: 1

jzd
jzd

Reputation: 23629

I like the idea of using the band id in the results table. I would suggest eliminating the "old band name" table and replace it with a table of band aliases, since that sounds more like what you want. The band alias table would just have the band id and an one alias per row.

Upvotes: 2

Related Questions