3

I am an beginner-to-intermediate level programmer and am trying to re-design a MySQL database for someone. It has the following attributes:

  • Shows start and end times for alcohol sales in an area
  • Times vary by day of week
  • Times vary for beer and wine vs. all types of alcohol (liquor, too).
  • Times vary by off-premises (i.e.: liquor store) vs. on-premises (i.e.: bar)

I tried researching this but couldn't find a clear answer. Currently he one huge table. Example columns are:

  • offwedbwstart (off-premises, Wednesday, beer & wine only, starting time)
  • onfriallend (on-premises, Friday, all alcohol types, end time)

I'm wondering if there isn't a better way to organize this table using arrays as entries or by using multiple tables. Does anyone have any ideas? Or is this simply a matter of preference? I'm also worried about the efficiency of using multiple tables.

Thank you!

Revision in response to comment (more detailed explanation):

Here's are the fields of the original table which I'm tasked with revising. It is all one big table:

id BIGINT(9) NOT NULL, - Primary Key
state TINYTEXT NULL, - Full State Name
county TINYTEXT NULL, - Full County Name
place TINYTEXT NULL, - Full Place Name
placetype TINYINT(2) NULL DEFAULT NULL, - Is the Place a County, City, etc.
format TINYINT(1) NULL DEFAULT NULL, - Not sure
multname TINYINT(1) NULL DEFAULT NULL, - Does the place have multiple names
multcounty TINYINT(1) NULL DEFAULT NULL, - Does the place cross multiple counties
population INT(8) NULL DEFAULT NULL, - Population
offsunallstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premsies (convenience stores, liquor stores, etc.), Sunday, All types of alcohol (including liquor), Start Time (military time integer, hours and minutes past midnight)
offsunallend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Sunday, All types of alcohol, End Time (military time integer, hours and minutes past midnight)
offsunbwstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Sunday, Beer and Wine only,Start Time
offsunbwend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Sunday, Beer and Wine only,End Time
offmonallstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Monday, All types of alcohol, Start Time
offmonallend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Monday, All types of alcohol, End Time
offmonbwstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Monday, Beer and Wine only,Start Time
offmonbwend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Monday, Beer and Wine only,End Time
offtueallstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Tuesday, All types of alcohol, Start Time
offtueallend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Tuesday, All types of alcohol, End Time
offtuebwstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Tuesday, Beer and Wine only,Start Time
offtuebwend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Tuesday, Beer and Wine only,End Time
offwedallstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Wednesday, All types of alcohol, Start Time
offwedallend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Wednesday, All types of alcohol, End Time
offwedbwstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Wednesday, Beer and Wine only,Start Time
offwedbwend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Wednesday, Beer and Wine only,End Time
offthuallstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Thursday, All types of alcohol, Start Time
offthuallend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Thursday, All types of alcohol, End Time
offthubwstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Thursday, Beer and Wine only,Start Time
offthubwend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Thursday, Beer and Wine only,End Time
offfriallstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Friday, All types of alcohol, Start Time
offfriallend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Friday, All types of alcohol, End Time
offfribwstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Friday, Beer and Wine only,Start Time
offfribwend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Friday, Beer and Wine only,End Time
offsatallstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Saturday, All types of alcohol, Start Time
offsatallend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Saturday, All types of alcohol, End Time
offsatbwstart SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Saturday, Beer and Wine only,Start Time
offsatbwend SMALLINT(4) NULL DEFAULT NULL, - Off-Premises, Saturday, Beer and Wine only,End Time
onsunallstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies (bars, restaraunts, etc.), Sunday, All types of alcohol, Start Time
onsunallend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Sunday, All types of alcohol, End Time
onsunbwstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Sunday, Beer and Wine only,Start Time
onsunbwend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Sunday, Beer and Wine only,End Time
onmonallstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Monday, All types of alcohol, Start Time
onmonallend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Monday, All types of alcohol, End Time
onmonbwstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Monday, Beer and Wine only,Start Time
onmonbwend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Monday, Beer and Wine only,End Time
ontueallstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Tuesday, All types of alcohol, Start Time
ontueallend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Tuesday, All types of alcohol, End Time
ontuebwstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Tuesday, Beer and Wine only,Start Time
ontuebwend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Tuesday, Beer and Wine only,End Time
onwedallstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Wednesday, All types of alcohol, Start Time
onwedallend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Wednesday, All types of alcohol, End Time
onwedbwstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Wednesday, Beer and Wine only,Start Time
onwedbwend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Wednesday, Beer and Wine only,End Time
onthuallstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Thursday, All types of alcohol, Start Time
onthuallend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Thursday, All types of alcohol, End Time
onthubwstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Thursday, Beer and Wine only,Start Time
onthubwend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Thursday, Beer and Wine only,End Time
onfriallstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Friday, All types of alcohol, Start Time
onfriallend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Friday, All types of alcohol, End Time
onfribwstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Friday, Beer and Wine only,Start Time
onfribwend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Friday, Beer and Wine only,End Time
onsatallstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Saturday, All types of alcohol, Start Time
onsatallend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Saturday, All types of alcohol, End Time
onsatbwstart SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Saturday, Beer and Wine only,Start Time
onsatbwend SMALLINT(4) NULL DEFAULT NULL, - On-Premsies, Saturday, Beer and Wine only,End Time
offexceptions TEXT NULL, - Off-Premises exceptions as a note
onexceptions TEXT NULL, - On-Premises exceptions as a not
source TEXT NULL, - Where did this information come from?
sourcelink TINYTEXT NULL, - Link for information
timezone TINYTEXT NULL, - TimeZone (EST, for example)
notes TEXT NULL - Other miscellaneous notes

As you can see this is kind of a data nightmare. I've been reading up on how to organize this better and some problems that come up are crossing over midnight for sales, and there is another problem of multiple days of the week having the same start and close times, to which I thought I could create a table that simply had the fields:

  • Start Day (weekday, integer representation)
  • End Day (weekday, integer representation)
  • Start Time (hours past midnight that morning, integer representation)
  • End Time (hours past midnight that morning, integer representation)

Please feel free to make any suggestions for a schema.

Thank you!

spiff
  • 308
  • 1
  • 3
  • 15
  • [Junction tables](http://stackoverflow.com/a/32620163) or association tables. No arrays in columns. Data normalization. Indexes used in queries versus wishful thinking – Drew Nov 22 '15 at 22:36
  • We need to know a bit more about what is actually stored in that database. It's probably a simple task normalizing the tables and queries, but from the samples you've provided we can't tell. So aside from the attributes, what actually goes into that database, and what do you seek to extract from it? – Gralgrathor Nov 23 '15 at 01:24
  • Revised the post. Hope that clears things up a bit. I'm seeking to extract the start and end times for the sales of alcohol in a given municipality for any given day of the week. There are variations in beer and wine vs. liquor and on-premises vs. off-premises purchases. – spiff Nov 23 '15 at 06:56

1 Answers1

1

Using multiple tables is one of the major strengths of using a relational database. Otherwise, go ahead and use a spreadsheet program instead. :-0

One flaw I see in that database structure is that finding, for example, Thursday's information requires knowing the specific field name of four fields. It would be better for the field names to be generic—no day of week in them—and add one field for the day of the week. That reduces the number of fields by a factor of almost seven: 28 fields are reduced to 5.

There might be some further data normalization possible with the geography fields, but it is tough to know without knowing more about how that is used. If there is a lot of interfacing to the field names, you already have a heap of work just to reducing it for day of week. Try revising the schema just for that much and you'll probably know enough to decide if more needs to be done or if it would be more work than it is worth.

wallyk
  • 56,922
  • 16
  • 83
  • 148
  • I sincerely wish to emphasize that I'm re-doing this for a lawyer who treated it like a spreadsheet and I told him that. Now it's my job to re-design it. I don't think it's anything that some PL/SQL scripts can't repair, but I want to have a solid schema first. Thank you for the DOW integer suggestion, though. I will employ it. – spiff Nov 23 '15 at 07:17
  • I have designed a schema that employs your suggestions and my research. I have posed another question related to the geography fields, which are incredibly complicated. – spiff Nov 23 '15 at 19:29