CODEWITHSUNDEEP
c#sqlsql-serversessionvisual-studio-2012
solidak
solidak

Reputation: 5081

Saving an array of strings to an SQL column

I want to create a "sessions" table in an SQL database for a school project. Each session should have:

How can I represent such a thing ???

Is it better to create ONE table to represent all sessions, where each session would be one row, and have an array of strings in each column that represents the names, IDs, and status of students??

OR

Create a new table for each new session ??

What is better, and please explain how to do it briefly.

Bear in mind, that I would need to insert/delete/update/view each table from a C# windows application, and the maximum expected number of sessions is just 100.

Also, I am using SQL Server 2012 with a C# windows application developed with Visual Studio 2012

Thanks

Upvotes: 0

Views: 5801

Answers (1)

Jay S.
Jay S.

Reputation: 1327

I think you should have 3 tables :

Students

------------------------------------
| StudentID | FirstName | LastName |
|-----------|-----------|----------|
|   4456    |   John    |   Doe    |
|   6678    |   Billy   |   Bob    |
------------------------------------

Here StudentID is Primary Key

Sessions

---------------------------------------------------------------------
| SessionID | Lecturer | DateTime |     Module       |    Course    |
|-----------|----------|----------|------------------|--------------|
|    1      |  Mr.Joe  | 524523461| Natural Sciences | Oceanography |
---------------------------------------------------------------------

Here dateTime would be a Unix Timestamp and SessionId is Primary Key

SessionAttendance

-------------------------------------
| SessionID | StudentID |  Status   |
|-----------|-----------|-----------|
|     1     |    4456   |  'Late'   |
|     1     |    6678   | 'Present' |
-------------------------------------

Here SessionID and StudentID are both Primary Keys

Reason

Here you don't need to parse all the lists of attendance and statuses. The queries may get a little bigger, but it will save you alot of parsing code.

Example Query :

SELECT SessionID FROM SessionAttendance WHERE StudentID  = (SELECT StudentID FROM Students WHERE FirstName = 'John' AND LastName = 'Doe') AND Status = 'Late';

It will get all the sessions in which John Doe was late. Simple, right?

Other Comments

  1. You cannot store arrays of information in an database column. It must be a list that is delimited somehow

  2. In your application, I reccomend you always keep the StudentID with its FirstName and LastName because it will make queries easier, and will keep the names localized (so that you can change it in one place and will change everywhere else)

Upvotes: 2

Related Questions