CODEWITHSUNDEEP
sql-serversql-server-2014
Hoube78
Hoube78

Reputation: 45

SQL Server : data type to use in a table

Might be a silly question to ask but what data type should I setup a column so I can enter multiple values?

Example: I have two tables, one called Application_users and the other Products.

Application_Users has an id column.

What I want is to have a column in Products which is called Application_Users_id and I enter 1,2,3,4

The idea is if an Application_User_id is say 3, they would only see products were the Products.Application_Users_ID contains a 3.

So what data type do I use so I can enter values such as 1,2,3,4 in a column?

I have tried NVARCHAR and INTEGER but neither work (NVARCHAR works but won't let me amend it e.g. add numbers).

Let me know what everyone thinks is the best approach here please.

Thanks John

Upvotes: 0

Views: 98

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

It might be a silly question but you would be surprised how many developers makes the very same mistake. It's so often that I have a ready-to-paste comment to address it:

Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!

And if you actually go and read this link, you'll see that it's so wrong and so frequently used that Bill Karwin addressed it in the first chapter of his book - SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

Having said that, SQL Server Does support XML columns, in which you can store multiple values, but that is not the case when you want to use them. XML columns are good for storing stuff like property bags, where you can't tell in advance the data types you'll have to deal with, for example.

tl;dr; - So what should you do?

What you want to do is probably a many to many relationship between Application_users and Products. The way to create a many to many relationship is to add another table that will be the "bridge" between the two tables - let's call it Application_users_to_products.
This table will have only two columns - application_user_id and product_id, each of them is a foreign key to the respective table, and the combination of both columns is the primary key of the bridge table.

Upvotes: 2

Related Questions