0

(In Azure SQL Server) I want to architect my database to handle INSERT and SELECT etc where the fields are (to me) comma separated values.

More specifically I don't know best practice to: 

SELECT * FROM products WHERE Currency = 

A.)  USD
b.)  USD or CAD
c.)  CAD or EUR
d.)  EUR
e.)  EUR or USD 
f.)  {Any}

Storing the Currency field in the products table as CSV is surely not efficient for SELECTs but since there are hundreds of currencies neither is an "intermediary table" (i.e. between products and currencies where each possible subset has an id). Thanks in advance

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
Mike S
  • 157
  • 3
  • 13

1 Answers1

0

In SQL, this would typically indeed be modeled using "intermediary tables". It is commonly known as a many-to-many relationship.

Check out this answer about a similar question:

A relationship is Many to Many if and only if One record from Table A is related to one or more records in a table B and vice-versa.

To establish a Many to Many relationship, Create a third table called "Relation" which will have the the primary keys of both the Tables A and B.

In your case you would have the tables

Product_ID    Product_Name
1             Laptop
2             Keyboard

Currency_ID   Currency_Name
1             USD
2             EUR

Product_ID    Currency_ID
1             1
1             2
2             2
Community
  • 1
  • 1
user3151902
  • 3,154
  • 1
  • 19
  • 32
  • Thanks for the reply. Is it correct then that for your above Product, Currency & Relation tables you then use an inner SUBquery like: "Select Distinct Product_ID from Relation where Currency_ID = [1 / 2 / 1 OR 2]". – Mike S Aug 25 '17 at 20:42