CODEWITHSUNDEEP
sql
Zainab Safiyyah Al-habsyi
Zainab Safiyyah Al-habsyi

Reputation: 79

Input multiple data into SQL column

I Plan to create a database that consists of these attributes

  1. id
  2. name
  3. job
  4. work_schedule

For example, I want to input Mike the Janitor, and he works every Wednesday, Thursday and Sunday. How do I input this into SQL effectively ? I've tried to use array (For example work_schedule = [3,4,7]). But, is there anything method that is easier ?

Upvotes: 1

Views: 83

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

First of all you are using Relational Databases, so don't use:

 work_schedule = [3,4,7]

Database normalization: 1 Normal Form:

A database is in first normal form if it satisfies the following conditions:

  • Contains only atomic values
  • There are no repeating groups

An atomic value is a value that cannot be divided.

Do not store multiple values in one column, unless you want to ask in future how to split CSV data and why it is so slow.

One way to go is multiple flags for every day:

id
name
job
is_working_Monday
is_working_Tuesday
is_working_Wednesday
...

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use bitwise operation.

use this values constant

Monday  = 1
Tuesday = 2
Wednesday = 4
Thursday = 8
Friday = 16
Saturday = 32
Sunday  = 64

Then workschedule for 3, 5, 7 will be 

SET workschedule = 4 + 16 + 64;

And select to get jobs on Wednesday will be

SELECT *
FROM YourTable
WHERE workschedule  & 4  > 0

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270401

This is actually an interesting question. There are a handful of methods. I can readily think of three, any of which might be appropriate given the circumstances.

  • Have a separate table WorkSchedule for each possible combination of days when someone could work.
  • Have a separate table of WorkerDays that has a separate row for each worker and each day when s/he could work.
  • Store the information in one row.

The middle one is the most "SQL-like" in the sense that it is normalized, and should be flexible for most needs.

The third alternative seems to be the path you are going down. A typical method is to store a separate flag for each day: MondayFlag, TuesdayFlag, etc.

An alternative method is to store the flags within a single column, using bit-masks to identify the information you want. Of course, this depends on the bit-fiddling capabilities of the database you are working with.

The actual choice of how to model the data depends on how it will be used. You need to think about the types of questions that will be asked about work days.

Upvotes: 1

Related Questions