#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    75
    Rep Power
    2

    How to you make tables for this


    I need to make a database for a mess.
    There are some 5-6 messes. Student can choose where he can will have his breakfast/lunch/dinner everyday.

    For eg:- On monday he can have breakfast at mess1, lunch at mess2 and dinner at mess3. Similarly other days he can have at different places. But weekly schedule will be same.

    So how do you store something like this?

    I just started data management so these things might be trivial
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    We've got mess facilities, students, mealtimes -- these are basic, independent ideas that don't depend on one another (I can know what a student is without knowing what a mess facility or a mealtime is). One table for each.

    We have event moments that represent a relationship among these ideas -- and each relationship idea relies on knowlege of the underlying three ideas above. Different mess facilities have different mealtime schedules. Students may be involved in no more than one of these schedule times per mealtime. This would be two more tables, one for the meal schedule and one for attendance against meal schedules.
    sql Code:
    CREATE TABLE mess
     (code  VARCHAR(20) PRIMARY KEY,
      name  VARCHAR(40) NOT NULL);
     
    -- HINT: Use the school-granted id, not some meaningless integer serial.
    -- You may want to include their name or whatever,
    -- but that's not really the point.
    CREATE TABLE student
     (id  VARCHAR(20) PRIMARY KEY);
     
    CREATE TABLE meal
     (code  VARCHAR(10) PRIMARY KEY);
     
    -- NOTE: Range types aren't supported by most databases.
    -- The common solution is to create "start_at" and "end_at" 
    -- timestamp fields instead.
    CREATE TABLE schedule
     (mess  VARCHAR(20) REFERENCES mess NOT NULL,
      meal  VARCHAR(10) REFERENCES meal NOT NULL,
      span  tsrange NOT NULL,
      PRIMARY KEY (mess, meal));
     
    CREATE TABLE attendance
     (student  VARCHAR(20) REFERENCES student NOT NULL,
      mess    VARCHAR(20) REFERENCES mess NOT NULL,
      meal    VARCHAR(10) REFERENCES meal NOT NULL,
      PRIMARY KEY (student, mess, meal));

    With a layout like this you guarantee non-duplication of data, all meaningful keys and the data you really want (knowing which students went where) require querying a single table -- just the "attendance" one. Most schemas recommended by ORM frameworks or other db tools will create a bunch of meaningless numerical id fields and function-based constraint checks that make things run slow and require a lot of joins or other logic to get your final answer. I strongly recommend against such things if you want the data model to produce intrinsically meaningful results on its own.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    so much for discouraging the farming out of verbatim homework questions to the forum...

    Comments on this post

    • zxq9 agrees : Touche
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    Touche. (~_~)

    In my defense, however, I contend that if this was a homework question it was at least somewhat camouflaged.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    well, i'm sort of divided on this issue (having seen it many times over the years)

    a well-camouflaged homework question is rewarded with a thorough answer, while an obvious homework question should get what, just hints?

    i think giving hints is probably best for both types *

    any student who wants to learn will run with the hints, and any working slob (i mean that in the nicest way) who just wants an answer should, really, be happy with the hints too

    what really frosts my cake is the guys (not naming any names, but there are a handful on this site) who ask literally (and i mean that literally) hunnerts of questions and hardly ever give anything back

    * although if it's a tricky problem with an elegant solution, i will happily bang out an entire query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by zxq9
    We've got mess facilities, students, mealtimes -- these are basic, independent ideas that don't depend on one another (I can know what a student is without knowing what a mess facility or a mealtime is). One table for each.

    We have event moments that represent a relationship among these ideas -- and each relationship idea relies on knowlege of the underlying three ideas above. Different mess facilities have different mealtime schedules. Students may be involved in no more than one of these schedule times per mealtime. This would be two more tables, one for the meal schedule and one for attendance against meal schedules.
    sql Code:
    CREATE TABLE mess
     (code  VARCHAR(20) PRIMARY KEY,
      name  VARCHAR(40) NOT NULL);
     
    -- HINT: Use the school-granted id, not some meaningless integer serial.
    -- You may want to include their name or whatever,
    -- but that's not really the point.
    CREATE TABLE student
     (id  VARCHAR(20) PRIMARY KEY);
     
    CREATE TABLE meal
     (code  VARCHAR(10) PRIMARY KEY);
     
    -- NOTE: Range types aren't supported by most databases.
    -- The common solution is to create "start_at" and "end_at" 
    -- timestamp fields instead.
    CREATE TABLE schedule
     (mess  VARCHAR(20) REFERENCES mess NOT NULL,
      meal  VARCHAR(10) REFERENCES meal NOT NULL,
      span  tsrange NOT NULL,
      PRIMARY KEY (mess, meal));
     
    CREATE TABLE attendance
     (student  VARCHAR(20) REFERENCES student NOT NULL,
      mess    VARCHAR(20) REFERENCES mess NOT NULL,
      meal    VARCHAR(10) REFERENCES meal NOT NULL,
      PRIMARY KEY (student, mess, meal));

    With a layout like this you guarantee non-duplication of data, all meaningful keys and the data you really want (knowing which students went where) require querying a single table -- just the "attendance" one. Most schemas recommended by ORM frameworks or other db tools will create a bunch of meaningless numerical id fields and function-based constraint checks that make things run slow and require a lot of joins or other logic to get your final answer. I strongly recommend against such things if you want the data model to produce intrinsically meaningful results on its own.
    That looks a little bit complicated
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    75
    Rep Power
    2
    I still haven't learn any query language so I am not familar with sql code.
    Anyways, I understood most of it. Why did you define four tables?
    Can't we do in two tables - one for student and other for mess.
    The student table has an array which stores in which mess he/she eats. Can't we do that?

    In general, how do you make tables for one to many relations.

    Please don't put any query-langauge specific code. I don't know the syntax yet, although it's very easy to understand
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Avichal
    The student table has an array which stores in which mess he/she eats. Can't we do that?
    you can, but it will give you a world of hurt

    do a search for first normal form

    Originally Posted by Avichal
    In general, how do you make tables for one to many relations.
    with foreign keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    Originally Posted by r937
    well, i'm sort of divided on this issue (having seen it many times over the years)...
    I strongly agree with your position on hints being healthy -- especially if they come back with a coded attempt based on those hints and want further discussion.
    * although if it's a tricky problem with an elegant solution, i will happily bang out an entire query
    And this is where the gray area comes in. For someone who doesn't know the domain all of these questions are tricky problems with hidden (or presently incomprehensible) elegant solutions. My difficulty with the homework problem is trying to decide who needs a nudge and who is looking for rote answers to write on their homework.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    Originally Posted by Avichal
    I still haven't learn any query language so I am not familar with sql code.
    Anyways, I understood most of it. Why did you define four tables?
    Can't we do in two tables - one for student and other for mess.
    The student table has an array which stores in which mess he/she eats. Can't we do that?
    I defined five, actually. Three are basic ideas: students, mess facilities, and mealtimes. There is one table that is a pure definition of a relationship between two basic ideas (the schedule), and there is one table that is a relationship between a basic idea (student) and a relation idea (schedule).

    You can't really break this problem down further, and clumping ideas together will require application code to do a lot of checking that can be completely avoided by using a data model whose rules remove the necessity to do manually coded checks.

    So yes, its possible to do this with fewer tables, but it is a very bad idea because it ignores the purpose of a relational database entirely. If you want to write a bunch of procedural code to do checks to make sure your data makes sense then you could just ignore the database entirely and store everything in a text file.

    But you will you wind up reinventing the features of a relational database in your own code. Its like saying you don't want to use "tool A" to drive "tack B" into a board -- the required motions look complicated and all you want to do is get this straight-hard thingy embedded in board anyway and you think you see a better/simpler way. And so you go off and invent something on your own that indeed can get a straight, hard thingy into a board. But you wind up inventing a hammer to replace "tool A", which was a screwdriver, with which you proceed to hit "tack B", which was a drywall screw. Sure, the tack eventually goes into the board, but the solution is messier and the end result unsuitable for practical use.

    On the other hand, most software developed today resembles this metaphor.
    In general, how do you make tables for one to many relations.

    Please don't put any query-langauge specific code. I don't know the syntax yet, although it's very easy to understand
    Every time up there I wrote the word "REFERENCES" the database system creates a rule that says "data cannot go into this field that does not already exist in the referenced table".

    So if we have a row in student whose id is "100" we can put a row in the attendance table where the student value is "100". If student id "101" does not exist, however, we cannot put a row in attendance where the id is "101", because that student doesn't exist.

    This is how you guarantee your data makes sense without having to write any code to check it.

    The same is true for the meal times. If we used fewer tables we wouldn't be able to guarantee that students aren't accidentally (or against the rules) visiting multiple mess facilities for each meal. If we needed such a guarantee we would need to write extra code that does a process like "before inserting a row in attendance for mealtime A at mess B, check that this student has no other entries for mealtime A on the same day". With the schema I presented you don't have to write any of that, you just schedule meals at messes, and students either attend them or they don't -- but you don't have to write any more code to guarantee that the data makes sense.

    In short, the integrity of the data is guaranteed to follow the rules by which the schema was formed. That is the whole point of foreign keys.

    The magical thing is that if you use natural keys and multi-column primary keys, most of the time the end-user's most frequent queries only require a query on a single table. For example, asking "what messes had a lunch on March 20th, 2012?" requires only a query on the schedule table. An integer ID scheme would require a JOIN between the mess and schedule table. Asking "how many meals has student #123 eaten in our mess facilities?" or even "how many breakfasts has student #123 eaten in our mess facilities?" require only querying the attendance table, and the data is meaningful even if you don't use any fancy front-end application -- the results make sense without any fancy processing in straight db answers. Using fewer tables or a more complex array-over-row scheme requires a good bit of processing to render an answer, and still is not guaranteed to be correct because there is no protection against arbitrarily wrong data.

    Blah blah blah. This response is basically an expanded version of r937's answer.

IMN logo majestic logo threadwatch logo seochat tools logo