CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-design
Lewis Cianci
Lewis Cianci

Reputation: 1065

What is the most correct way to store a "list" in a SQL Database?

So, I've read a lot about how stashing multiple values into one column is a bad idea and violates the first rule of data normalisation (which, surprisingly, is not "Do Not Talk About Data Normalisation") so I need some help.

At the moment I'm designing an ASP .NET webpage for the place I work for. I want to display data on a web page depending on what Active Directory groups the person belongs to. The first way of doing this that comes to mind is to have a table with, essentially, a column containing the AD group and the second column containing what list of computers belong to that list.

I've learnt that this is showing great disregard for relational databases, so what is a better way to do it? I want to control this access by SQL tables, so I can add/remove from these tables and change end users access accordingly.

Thanks for the help! :)

EDIT: To describe exactly what I want to do is this:

We have a certain group of computers that need to be checked up on, however these computers are in physically difficult to reach locations. The organisation I belong to has remote control enabled for these computers, however they're not in the business of giving out the remote control password (understandable).

The added layer of complexity is that, depending on who you are, our clients should only be able to see a certain group of computers (that is, the group of computers that their area owns). So, if Group A has Thomas in it, and Group B has Jones in it, if you belong to either group then you would just see one entry. However, if you belong to both groups you should see both Thomas and Jones computers in it.

The reason why I think that storing this data in a SQL cell is the way to go is because, to store them in tables would require (in my mind) a new table for each new "group" of computers. I don't want to crank out SQL tables for every new group, I'd much rather just have an added row in a SQL table somewhere.

Does this make any sense?

Upvotes: 7

Views: 17450

Answers (7)

rubenrb
rubenrb

Reputation: 76

You can have a table with the group and group id, another table with the computer and computer id and a third table with the relation of group id and computer id.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You basically have three options in SQL Server:

  • Storing the values in a single column.
  • Storing the values in a junction table.
  • Storing the values as XML (or as some other structured data format).

(Other databases have other options, such as arrays, nested tables, and JSON.)

In almost all cases, using a junction table is the correct approach. Why? Here are some reasons:

  • SQL Server has (relatively) lousy string manipulation, so doing something as simple as ensuring a unique list is really, really hard.
  • A junction table allows you to store lots of other information (When was a machine added? What is the full description of the machine? etc. etc.).
  • Most queries that you want are pretty easy with a junction table (with the one exception of getting a comma-delimited list, alas -- which is just counterintuitive rather than "hard").
  • All the types are stored natively.
  • A junction table allows you to enforce constraints (both check and foreign key) on the elements of the list.

Although a delimited list is almost never the right solution, it is possible to think of cases where it might be useful:

  • The list doesn't change and presentation of the list is very important.
  • Space usage is an issue (alas, denormalization often results in fewer pages).
  • Queries do not really access elements of the list, just the entire thing.

XML is also a reasonable choice under some circumstances. In the most recent versions of SQL Server, this can be made pretty efficient. However, it incurs the overhead of reading and parsing XML -- and things like duplicate elimination are still not obvious.

So, you do have options. In almost all cases, the junction table is the right approach.

Upvotes: 8

Jesse Webb
Jesse Webb

Reputation: 45243

This sounds like a typical many-to-many problem. You have many groups and many computers and they are related to eachother. In this situation, it is often recommended to use a mapping table, a.k.a. "junction table" or "cross-reference" table. This table consist solely of the two foreign keys in your other tables.

If your tables look like this:

Computer
- computerId
- otherComputerColumns

Group
- groupId
- othergroupColumns

Then your mapping table would look like this:

GroupComputer
- groupId
- computerId

And you would insert a single record for every relationship between a group and computer. This is in compliance with the rules for third normal form in regards to database normalization.

Upvotes: 0

Carlos Delgado
Carlos Delgado

Reputation: 336

I not really a bad idea to store multiple values in one column, but will depend the search you want.

If you just only want to know the persons that is part of a group then you can store persons in one column with a group id as key. For update you just update the entire list in a group.

But if you want to search a specified person that belongs to group, then its not recommended that you store this multiple persons in one column. In this case its better to store a itermedium table that store person id, and group id.

Upvotes: 1

Matthew Whited
Matthew Whited

Reputation: 22433

There is an "it depends" that you should consider. If the data is never going to be queried (or queried very rarely) storing it as XML or JSON would be perfectly acceptable. Many DBAs would freak out but it is much faster to get the blob of data that you are going to send to the client than to recompose and decompose a set of columns from a secondary table. (There is a reason document and object databases are becoming so popular.)

... though I would ask why are you replicating active directory to your database and how are you planning on keeping these in sync.

Upvotes: 1

virtouso
virtouso

Reputation: 569

a list has some columns like: name, family name, phone number etc. and rows like name=john familyName= lee number=12321321 name=... familyname=... number=...

an sql database works same way. every row in a sql database is a record. so you jusr add records of your list into your database using insert query. complete explanation in here: http://www.w3schools.com/sql/sql_insert.asp

Upvotes: 0

WebMasterP
WebMasterP

Reputation: 518

Sounds like you want a table that maps users to group IDs and a second table that maps group IDs to which computers are in that group. I'm not sure, your language describing the problem was a bit confusing to me.

Upvotes: 0

Related Questions