CODEWITHSUNDEEP
sql-serverms-access
Mike
Mike

Reputation: 69

SQL Server to allow multiple values in a column

I'm using SQL Server as backend and MS Access as a front end, I can create a table in Access with lookup wizard which will allow me to create combo box in the form and select multiple values in the drop down, for example,

Lookup value definition, Drama, adventure, horror etc., in combo box I can select multiple genres for a movie and store them in a single cell on the table.

How to replicate the same with SQL Server as back end, I tried entering multiple value in default value and it won't work. Is this possible with SQL Server?

Upvotes: 2

Views: 1424

Answers (1)

Adwaenyth
Adwaenyth

Reputation: 2110

Entering many values into a single column is a big no no in relational database design.

What you are trying to do here is to realize a many to many relationship. You have multiple items, films or whatever and you have multiple genres.

That is the classical case of a many to many relationship. Usually in such a case you have 3 tables, one representing the items, another representing the genres. In between you've got a third table that has foreign keys to the items and the genres.

In the end you will end up with a table structure like this:

Items                  ItemToGenre               Genres
ID | item              ItemID | GenreID          ID | genre

And have foreign keys on ItemToGenre.ItemID from Items.ID and on ItemToGenre.GenreID to Genres.ID.

Then you can JOIN your tables to get all genres any specific item has. And do not break atomization.

Upvotes: 3

Related Questions