Take the 2-minute tour ×
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's 100% free, no registration required.

enter image description here

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.

share|improve this question
    
Well, an obvious question would be what are you designing for? My only direct observation would be the phones table, I never found a need to separate a number that much. For me this is over-normalization. PS: down-voter, please, a comment on what could be improved so that the question is clearer? Thanks. –  Marian Mar 23 '13 at 11:42
    
Your addresses table is US centric. Will likely need more columns to accommodate global address formats. However, this may be no issue depending on the purpose of this schema. Can you elaborate? –  Thronk Mar 23 '13 at 14:19
    
Thanks everyone for the feedback. This schema will feed a system where Multiple stores in multiple districts in multiple territories each have multiple Sales Managers. It will then track their sales numbers (data table) for a variety of categories (datatype table). End result will be a shiny table on a website :). I see what you mean with the phone number. I think I will implode it a little bit. –  Cory Scott Cline Mar 23 '13 at 16:12
    
The idea of a global address table is one I did not think of. However, all my locations are US based (I personally control who is registered to use it as well), so for now at least, it will be ok I think.I have since modified the schema a little bit. I think it's ready for use :) –  Cory Scott Cline Mar 23 '13 at 16:13
    
The weekStart and weekEnd could be DATE, no need for DATETIME. And weekEnd should always be weekStart + 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 @dateToCheck being either DATE or DATETIME. –  ypercube Mar 23 '13 at 16:59

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks for the straightforward answer. I see clearly now :) –  Cory Scott Cline Mar 23 '13 at 16:29

enter image description here

            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","CorySCline@YoMama.com","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");
share|improve this answer
    
I went ahead and posted my new schema with some sample data for any future visitors. Maybe it will spark ideas :) –  Cory Scott Cline Mar 23 '13 at 16:26
3  
You could make an SQL-Fiddle (with both CREATE TABLE and INSERT statements), and copy a link to it, in your question. –  ypercube Mar 23 '13 at 16:42
    
@ypercube - +1 on the comment for SQL-Fiddle. Useful stuff! –  Thronk Apr 2 '13 at 21:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.