Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Anything obvious that could be improved upon? :) I am positive there are no direct errors. More what I am looking for is errors in normalization and areas that I could simplify data storage. Please see my previous question for the context and motivation.
In addition to the rather odd (and possibly over-restrictive modeling of the phone number components), the "data" concerns me. It basically looks like the EAV pattern restricted to DECIMAL data type, which can have it's place, however there appears to be NO OTHER data modeled at all.
When you finally get round to querying these things, it's a lot easier and involves less pivoting if you can just do things like SELECT revenue - expenses AS profit or whatever it is these different types of data are that are attached to each user each week.
INSERT INTO weeks (weekID, weekStart, weekEnd)
VALUES ("1","2012-12-30 00:00:00","2013-01-05 00:00:00"),
("2","2013-01-06 00:00:00","2013-01-12 00:00:00"),
("3","2013-01-13 00:00:00","2013-01-19 00:00:00"),
("4","2013-01-20 00:00:00","2013-01-26 00:00:00"),
("5","2013-01-27 00:00:00","2013-02-02 00:00:00"),
("6","2013-02-03 00:00:00","2013-02-09 00:00:00"),
("7","2013-02-10 00:00:00","2013-02-16 00:00:00"),
("8","2013-02-17 00:00:00","2013-02-23 00:00:00"),
("9","2013-02-24 00:00:00","2013-03-02 00:00:00"),
("10","2013-03-03 00:00:00","2013-03-09 00:00:00"),
("11","2013-03-10 00:00:00","2013-03-16 00:00:00"),
("12","2013-03-17 00:00:00","2013-03-23 00:00:00"),
("13","2013-03-24 00:00:00","2013-03-30 00:00:00"),
("14","2013-03-31 00:00:00","2013-04-06 00:00:00"),
("15","2013-04-07 00:00:00","2013-04-13 00:00:00"),
("16","2013-04-14 00:00:00","2013-04-20 00:00:00"),
("17","2013-04-21 00:00:00","2013-04-27 00:00:00"),
("18","2013-04-28 00:00:00","2013-05-04 00:00:00"),
("19","2013-05-05 00:00:00","2013-05-11 00:00:00"),
("20","2013-05-12 00:00:00","2013-05-18 00:00:00"),
("21","2013-05-19 00:00:00","2013-05-25 00:00:00"),
("22","2013-05-26 00:00:00","2013-06-01 00:00:00"),
("23","2013-06-02 00:00:00","2013-06-08 00:00:00"),
("24","2013-06-09 00:00:00","2013-06-15 00:00:00"),
("25","2013-06-16 00:00:00","2013-06-22 00:00:00"),
("26","2013-06-23 00:00:00","2013-06-29 00:00:00"),
("27","2013-06-30 00:00:00","2013-07-06 00:00:00"),
("28","2013-07-07 00:00:00","2013-07-13 00:00:00"),
("29","2013-07-14 00:00:00","2013-07-20 00:00:00"),
("30","2013-07-21 00:00:00","2013-07-27 00:00:00"),
("31","2013-07-28 00:00:00","2013-08-03 00:00:00"),
("32","2013-08-04 00:00:00","2013-08-10 00:00:00"),
("33","2013-08-11 00:00:00","2013-08-17 00:00:00"),
("34","2013-08-18 00:00:00","2013-08-24 00:00:00"),
("35","2013-08-25 00:00:00","2013-08-31 00:00:00"),
("36","2013-09-01 00:00:00","2013-09-07 00:00:00"),
("37","2013-09-08 00:00:00","2013-09-14 00:00:00"),
("38","2013-09-15 00:00:00","2013-09-21 00:00:00"),
("39","2013-09-22 00:00:00","2013-09-28 00:00:00"),
("40","2013-09-29 00:00:00","2013-10-05 00:00:00"),
("41","2013-10-06 00:00:00","2013-10-12 00:00:00"),
("42","2013-10-13 00:00:00","2013-10-19 00:00:00"),
("43","2013-10-20 00:00:00","2013-10-26 00:00:00"),
("44","2013-10-27 00:00:00","2013-11-02 00:00:00"),
("45","2013-11-03 00:00:00","2013-11-09 00:00:00"),
("46","2013-11-10 00:00:00","2013-11-16 00:00:00"),
("47","2013-11-17 00:00:00","2013-11-23 00:00:00"),
("48","2013-11-24 00:00:00","2013-11-30 00:00:00"),
("49","2013-12-01 00:00:00","2013-12-07 00:00:00"),
("50","2013-12-08 00:00:00","2013-12-14 00:00:00"),
("51","2013-12-15 00:00:00","2013-12-21 00:00:00"),
("52","2013-12-22 00:00:00","2013-12-28 00:00:00");
INSERT INTO territories (territoryID, territoryManager)
VALUES ("1","James Bond");
INSERT INTO districts (districtID, territoryID, districtManager)
VALUES ("27","1","DistrictManager 27"),
("28","1","DistrictManager 28"),
("29","1","DistrictManager 29"),
("30","1","DistrictManager 30"),
("31","1","DistrictManager 31"),
("32","1","DistrictManager 32");
INSERT INTO stores (storeID, districtID, storeManager)
VALUES ("0727","27","Craig S List"),
("1699","27","D27S1699Manager"),
("5864","28","D28S5864Manager"),
("5745","29","D29S5745Manager"),
("1700","30","D30S1700Manager"),
("4231","30","D30S4231Manager");
INSERT INTO ranks (name)
VALUES ("Administrator"),
("Territory Manager"),
("District Manager"),
("Store Manager"),
("Sales Specialist");
INSERT INTO users (userID, storeID, name, rankID, photoPath, active, emailPreference, dateAdded)
VALUES ("123456","0727","Cory Cline","1","123456.jpg","1","[email protected]","2013-03-23 05:44:43");
INSERT INTO addresses (storeID, street, street2, city, state, zip)
VALUES ("0727","7982 NW RR Road","","Kansas City","MO","64151"),
("1699","123 James Road","Suite 100","Kansas City","KS","65415"),
("5864","247 Washer Lane","","King City","MO","64108"),
("5745","20938 Highland Drive","","Riverside","MO","64151"),
("1700","54 90th Terrace","#4","Kansas City","MO","64151"),
("4231","666 Hello Moto World Lane","","China Town","NY","00000");
INSERT INTO datatypes(name)
VALUES ("Warranties"),
("Services"),
("Credit Cards");
INSERT INTO data (userID, week, data, datatypeID, dateEntered)
VALUES ("123456","12","5460.65","1","2013-03-23 05:44:43"),
("123456","12","34.65","2","2013-03-23 05:44:43"),
("123456","12","5","3","2013-03-23 05:44:43");
INSERT INTO logins (username, password, userID)
VALUES ("CorySCline","46tL5l/YDMUHE",,"123456");
INSERT INTO phones (storeID, phoneNumber)
VALUES ("0727","8167168977"),
("1699","7859139567"),
("5864","8168755555"),
("5745","8168257777"),
("1700","8169136457"),
("4231","816124", "7657");
CREATE TABLE and INSERT statements), and copy a link to it, in your question.
Mar 23, 2013 at 16:42
weekStartandweekEndcould beDATE, no need forDATETIME. AndweekEndshould always beweekStart + INTERVAL 7 DAYS(not 6 as you have in your data now). Date comparisons could then be of the type:WHERE @dateToCheck >= weeks.weekStart AND @dateToCheck < weeks.weekEnd. That should work with@dateToCheckbeing eitherDATEorDATETIME.