Structure table day and times (schedule type)

Asked

Viewed 2,377 times

4

I need to create a table that contains days and hours of operation of an establishment, a kind of agenda. This table should represent every day of the week and on each day contain the opening and closing hours. The challenge is to create this table in a way that does not have redundant data and that facilitates the search for the opening hours of a given day.

For example, I will need to pick up the day of operation of the establishment next Wednesday, or today’s time. There is still another factor that are the holiday days (that the schedule will certainly be different from the normal days, taking into account that the establishment is open). Facebook has something similar on the corporate pages.

In the image a representation of the idea (the first time is the opening time and the second the closing time). I will use Mysql.

inserir a descrição da imagem aqui

mysql sql mysqli table

  • How are you going to use this? The always fixed sermon times for the specified days ? Your fields will be of what type? string ? time ??

  • Theoretically the times will be fixed. Once defined, with some exception, they will not be changed anymore. As for the fields, I have not defined the type.

  • and how will you show it in the same way as this in your table above? side by side times ?

  • No. In fact I will filter the "open" establishments according to the day the system is accessed. p.e.: Monday, 19:20 hours, the user accesses the system and will see only the companies opened that day and time. In the case of the admin of the establishment/company, it needs to have a global view of the days and times.

  • Then you have a company direct relation 1----> Many schedules, if you create fields with the varchar() type for the schedules you could create your table with the fields you specified above by adding the company’s FK in the table.

  • See if the answer meets what you need.

  • @Gokussjgod I am testing an option very similar to the one you indicated to me, with some small changes. As soon as I finish I give my feedback.

  • Okay, see that the database used in the answer is sql server, but the logic would be the same for your database. when I’ve had more time riding something on the fiddle

  • @Gokussjgod, I followed a little of what you suggested with the proposed answer in that answer (http://answall.com/questions/44452/gravar-no-banco-dados-o-hor%C3%A1rio-de-expediente-e-realizar-uma-consulta?answertab=active#tab-top) - which are quite similar, by the way. So far you’re treating me well.

  • You managed to solve your problem?

  • @Danilomiguel if I could help with my answer, you can accept the answer by clicking on the left side of it. If you need any more help, let us know

Show 6 more comments

3 answers

1


According to comments you described, you have a relationship between COMPANY and Schedules of 1 p/ Many, that way I’d do it this way.

declare @Empresa table
(
    id int,
    nome varchar(50)
)


declare @Horarios table
(
    id int,
    idEmpresa int,
    diaSemana varchar(20),
    abertura time,
    fechamento time
)

insert into @Empresa values
(1, 'X'),
(2, 'Y')

insert into @Horarios values
(1,1, 'segunda-feira', '08:00', '18:00'),
(2,1, 'terca-feira', '08:00', '18:00'),
(3,1, 'quarta-feira', '08:00', '18:00'),
(4,1, 'quinta-feira', '08:00', '18:00'),
(5,1, 'sexta-feira', '08:00', '18:00'),
(6,1, 'sabado', '09:00', '17:00'),
(7,1, 'domingo', '09:00', '15:00'),
(8,1, 'feriado', '10:00', '13:00'),
(9,2, 'segunda-feira', '08:00', '18:00'),
(10,2, 'terca-feira', '08:00', '18:00'),
(11,2, 'quarta-feira', '08:00', '18:00'),
(12,2, 'quinta-feira', '08:00', '18:00'),
(13,2, 'sexta-feira', '08:00', '18:00'),
(14,2, 'sabado', '08:00', '18:00'),
(15,2, 'domingo', '09:00', '17:00'),
(16,2, 'feriado', '09:00', '16:00')

declare @horaInformada time = '10:00';

select nome, diaSemana, abertura, fechamento
from @Empresa e
join @Horarios h
on h.idEmpresa = e.id
where e.id = 2
and diaSemana = 'feriado'
and @horaInformada between abertura and fechamento

But I already make it clear that there may be several contradictory answers to this, because your question I would classify as (mainly based on opinions)... each can specify a different way of doing this.

0

The best way would be:

id_dia(Primary key) -> int
horario -> varchar(15)

In id_dia you enter the day number of the corresponding week, and the working time. id_dia must be the number that corresponds to the day of the week, example:

Id_dia

(1) for Sunday

(2) for Monday

(3) to Tuesday

...

(7) for Saturday

-2

I advise to make three tables:

The first table will be to insert the days of the week, for this you create two fields:

semana -> Nome Tabela
s_id   -> int
s_name -> varchar

The second table will be to enter the opening and closing times, for this you create 4 fields:

horarios   -> Nome Tabela
h_id       -> int
h_s_id     -> int
h_abertura -> time
h_fecho    -> time

The third table will be to insert the holidays, for this you create 5 fields:

feriados     -> Nome Tabela
f_id         -> int
f_s_id       -> int
f_diaferiado -> datetime
f_abertura   -> time
f_fecho      -> time

Then to return the values of a specific day just use one join, example:

SELECT * FROM semana 
LEFT JOIN horarios ON horarios.h_s_id=semana.s_id
WHERE s_name=domingo

Browser other questions tagged mysql sql mysqli table

You are not signed in. Login or sign up in order to post.