0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hoube78
  • 45
  • 6
  • 6
    ya, don't do that at all, ever, if possible. [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/2333499) – SqlZim Sep 06 '17 at 20:06
  • Usually you have a detail table for that. Also, consider groups of users. – KeithL Sep 06 '17 at 20:08
  • 3
    It's not a silly question to ask, it's a thing many developers get wrong. The answer is to *not* use a column for that -- use a table, that's what they're there for. `CREATE TABLE ProductsApplicationUsers(ProductID INT FOREIGN KEY REFERENCES Products(ID), ApplicationUserID INT FOREIGN KEY REFERENCES Application_users(ID), CONSTRAINT PK_ProductsApplicationUsers_ProductID_ApplicationUserID PRIMARY KEY(ApplicationUserID, ApplicationUserID)` with variations. – Jeroen Mostert Sep 06 '17 at 20:11
  • To add to what others have said, storing arrays or CSV as a single value is a violation of 1NF. It's a recipe for massive amount of pain and performance problems in the future. – Jason A. Long Sep 06 '17 at 20:15
  • There's a big error in my `CREATE TABLE` statement -- I'm going to pretend it was deliberate to see if the reader's paying attention. :-P The idea of an associative table is clear, in any case, and can be found in any decent course on database design. – Jeroen Mostert Sep 06 '17 at 20:18
  • Thank you for the quick response. Sorry I'm still a little bit confused as to the best approach. I have in the past always grouped by using the 1,2,3,4 (which I now know is wrong now) I have done this on MYSQL not MSSQL. – Hoube78 Sep 06 '17 at 20:39
  • 1
    MySql is, IMHO, not a very good rdbms. I know a lot of people likes it, but it have some bugs/features that drive me nuts. One of them is the fact that it's perfectly fine to create check constraints, however they are not enforced by the database. – Zohar Peled Sep 06 '17 at 20:43
  • Thank you all I have learnt something tonight. Now I just have to get my head around the logic and how it works. How does creating this 3rd table solve my issue? e.g. Product 1 is assigned to users 1,2,3,4? – Hoube78 Sep 06 '17 at 20:54

1 Answers1

2

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.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Hi, can a many to many relationship table have more than two columns or do I have to create multiple many to many tables? I'm looking at my data and found that I have the same issue in other areas now as well. – Hoube78 Sep 07 '17 at 13:00
  • It can have more than two columns, but you need to use a different table for every many to many relationship. However, not all relationship are many to many. Some may be simply one to many and in that case you just use a simple foreign key. – Zohar Peled Sep 07 '17 at 13:07