A question about the DB construction
-
Hi! I have worked with a DB programming, but this is the first time that I actually build a database on my own. Here is a short description of the task: I need to store the list of small businesses, each of them has some fields, like "Company name", "Address", "Phone", etc. However, I also need to store the working schedule for these businesses and that is something that I don't know how to implement. The problem is that businesses in my country often have different working hours and lunch breaks, for example. Monday - Friday: 10.00 - 19.00 , lunch break 13.00-14.00 Saturday: 10.00-16.00, lunch break 12.30-13.30; Sunday: OFF Because of the variety of the companies, I'd like to be able to take care of such instances. So far I came up with idea of using a table, which stores the schedule in 28 different columns (time of work start + time of work end + time of lunch start + time of lunch end) * 7 days = 28 columns. I was wondering if someone has done something similar before and can give me an advice on this. Thanks for help
-
Hi! I have worked with a DB programming, but this is the first time that I actually build a database on my own. Here is a short description of the task: I need to store the list of small businesses, each of them has some fields, like "Company name", "Address", "Phone", etc. However, I also need to store the working schedule for these businesses and that is something that I don't know how to implement. The problem is that businesses in my country often have different working hours and lunch breaks, for example. Monday - Friday: 10.00 - 19.00 , lunch break 13.00-14.00 Saturday: 10.00-16.00, lunch break 12.30-13.30; Sunday: OFF Because of the variety of the companies, I'd like to be able to take care of such instances. So far I came up with idea of using a table, which stores the schedule in 28 different columns (time of work start + time of work end + time of lunch start + time of lunch end) * 7 days = 28 columns. I was wondering if someone has done something similar before and can give me an advice on this. Thanks for help
I would suggest to accomplish this by using relational tables. Have a table with the business info, and then give the business an Id number. Create a table that has BusinessId,Day, TimeWorkStart, TimeWorkEnd, TimeLunchStart, TimeLunchEnd (or whatever you want to call them). The BusinessID would be the foreign key that would refer to the Business info table. To get the schedule, you would need to join up on the BusinessId of both tables. The way you listed above could be one approach (and I've seen similar things done in program), but you might come to a point where the design restricts you from adding additional schedules without storing a bunch of duplicate data. Also less data is being stored. If your table has 28 columns, but only has, say Monday and Tuesday workdays, then there are 20 unused columns.
-
I would suggest to accomplish this by using relational tables. Have a table with the business info, and then give the business an Id number. Create a table that has BusinessId,Day, TimeWorkStart, TimeWorkEnd, TimeLunchStart, TimeLunchEnd (or whatever you want to call them). The BusinessID would be the foreign key that would refer to the Business info table. To get the schedule, you would need to join up on the BusinessId of both tables. The way you listed above could be one approach (and I've seen similar things done in program), but you might come to a point where the design restricts you from adding additional schedules without storing a bunch of duplicate data. Also less data is being stored. If your table has 28 columns, but only has, say Monday and Tuesday workdays, then there are 20 unused columns.
Thanks a lot for your help! I really appreciate it. I liked your idea, but once I started to implement it, I've come up with a "joined" solution, that combines the advantages of yours and mine approaches. Here is the idea. Although, there is a big variety of businesses in my city, many of them share the same schedule. Therefore, I think it would be better to create a table "Schedules", which will contain the list of all schedules, that the businesses use. This way, each of 24/7 shops in my database will contain only an ID for appropriate 24/7 schedule, therefore, I will avoid lots of duplication. And, if I meet some odd schedule, I can just add it into the table with the schedules. If you find any faults in this idea, or have some suggestions - I'll appreciate that. Once again, thanks for time&effort
-
Thanks a lot for your help! I really appreciate it. I liked your idea, but once I started to implement it, I've come up with a "joined" solution, that combines the advantages of yours and mine approaches. Here is the idea. Although, there is a big variety of businesses in my city, many of them share the same schedule. Therefore, I think it would be better to create a table "Schedules", which will contain the list of all schedules, that the businesses use. This way, each of 24/7 shops in my database will contain only an ID for appropriate 24/7 schedule, therefore, I will avoid lots of duplication. And, if I meet some odd schedule, I can just add it into the table with the schedules. If you find any faults in this idea, or have some suggestions - I'll appreciate that. Once again, thanks for time&effort
Something that might happen in the future is that a business might need to have 2 types of schedules, causing duplicate data in the business table on every column but ScheduleId. You could "map" BusinessId's to SchedulesId's in a view, so you might have tables like: BusinessID, Name, ... ScheduleId, Day, StartTime, EndTime Mapping Table: BusinessId, ScheduleId (with a unique index on BusinessId, ScheduleID so you don't dupe data) Then have a view
Select ... From MappingTable m Join ...But if you have a business rule similar to one business can have one schedule per day, then I think your Joined solution will work fine.