Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / General DB Topics / General DB Topics / December 2004

Tip: Looking for answers? Try searching our database.

MySQL Database Structure

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
MJ Hendrikx - 24 Aug 2004 08:21 GMT
Hi,

We're planning a web application which
will allow teachers to plan their teacher diary online.
So for instance:
Mondag 8.50 - 9.40 --> Math (First Grade)
Mondag 9.40 - 10.30 --> French (Sixth Grade)
And so on for an entire week.
We have made some simple applications using only
a limited number of tables. But we're having some
trouble with the database structure for this one.
More specific: we're guessing we can't use one table
for the entire week schedule of all the teachers. But
do we have to make a new table for every teacher or
a new table for each day?
We would appreciate it if somebody could advise us
on our database structure.

Thanks,
MJ
jerry gitomer - 24 Aug 2004 14:34 GMT
> Hi,
>
[quoted text clipped - 16 lines]
> Thanks,
> MJ
MJ,

There is no reason why you can't have one table for all of
your teachers for a week.  If you have ten class periods
per day, operate on a five day week and have one hundred
teachers the table will have only 5000 rows.  Each row in
the table should contain; Teacher_ID, Day_of_Week, Time_of_Day,
and Dairy_Entry and the primary index should consist of the
first three columns.

HTH

Jerry
MJ Hendrikx - 24 Aug 2004 15:11 GMT
>> Hi,
>>
[quoted text clipped - 30 lines]
>
> Jerry
So if I understand correct, you would have:
1 monday 8.50 math
1 monday 9.40 french
2 monday 8.50 gym
1 tuesday 10.30 cooking
...
I don't quite understand what you mean by the primary index
consisting of the first 3 columns?
Thanks for your reply!
Ed prochak - 27 Aug 2004 17:01 GMT
> >> Hi,
> >>
[quoted text clipped - 39 lines]
> consisting of the first 3 columns?
> Thanks for your reply!

He means PRIMARY KEY. Some DBMS automatically create an index for the
PK, other DBMS make developers create it explicitly.

Your questions in this thread and others makes me wonder: do you have
any Database development training or experience? If not, you need some
fast. Or bring in a consultant. I can recommend a very good one. (No,
not me, I'm currently very busy with my own work.)

database systems are not files systems. the logic is VERY DIFFERENT.

HTH,
 Ed Prochak
 Magic Interface, Ltd.
 440-498-3700
Scott J. McCaughrin - 28 Dec 2004 03:34 GMT
: MJ Hendrikx <mjhendrikx@yahoo.stopthespam.com> wrote
: > >
[quoted text clipped - 41 lines]
: > consisting of the first 3 columns?
: > Thanks for your reply!

: He means PRIMARY KEY. Some DBMS automatically create an index for the
: PK, other DBMS make developers create it explicitly.

: Your questions in this thread and others makes me wonder: do you have
: any Database development training or experience? If not, you need some
: fast. Or bring in a consultant. I can recommend a very good one. (No,
: not me, I'm currently very busy with my own work.)

: database systems are not files systems. the logic is VERY DIFFERENT.

You are wrong on all counts. Several database systems are based upon
an OS's underlying file system, for one thing. For another, the response
to the OP was correct: there was no call for your sarcastic response.

To the OP: it seems like you want one table whose records look like:

             (teacher-ID,  hour,  day,  course).

For a case this simple, you don't need to resort to entity-relationship
(ER) diagrams: just apply some common sense. A teacher cannot be in two
places at once, so you cannot have both:

               (teacher-A, 9:40am, Monday, course-X) and
               (teacher-A, 9:40am, Monday, course-Y).

Therefore, the triple: (teacher-ID, hour, day) must appear uniquely in
your table -- which is to say, it is a primary key (PK). This means there
is a "primary-key constraint" which should be enforced:

  1) for Insert's, a new record should not be allowed with the same triple
     as another record in the table;

  2) for Update's, an existing record should not be changed to one with
     the same triple as another record in the table.

As to the issue of privacy, this can be handled by your DBA.
For instance, in INFORMIX, if the name of your table is "Teachers":

             REVOKE ALL on Teachers from PUBLIC;
             GRANT SELECT (teacher-ID, course) on Teachers TO phyllis

would limit access to the user with ID = "phyllis".
david epsom dot com dot au - 31 Dec 2004 04:13 GMT
> : database systems are not files systems. the logic is VERY DIFFERENT.
>
>  You are wrong on all counts. Several database systems are based upon
>  an OS's underlying file system,

You can go further: several OS's support db operations as a native
part of the file system.

AFAIK, Unix never included database primitives in the file system,
which may be the source of confusion.

(david)

> : > > MJ Hendrikx wrote:
> : > >
[quoted text clipped - 84 lines]
>
> would limit access to the user with ID = "phyllis".
Ed Prochak - 31 Dec 2004 15:11 GMT
[]
> : > ...
> : > I don't quite understand what you mean by the primary index
[quoted text clipped - 14 lines]
>  an OS's underlying file system, for one thing. For another, the response
>  to the OP was correct: there was no call for your sarcastic response.

on All counts? Oh please

What the heck does my comment about Primary keys and creating indices
have to do with File systems???

And where was I sarcastic? I asked about his background. I did make an
observation that based on his postings he seems to lack DB training. Do
you disagree with the idea that one should understand the basic
principles of the IT tools one is using?

I'm concerned about his training because
misdesigning systesm can cause his employer more than the cost of a
good class.
if we know his background better we can provide more tailored
responses.

Finally, why are you responding in December to a post I made back in
August?
<sarcasm mode ON>
are you a trollbot? your response was 4months TO THE DAY of my post.
Just coincidence?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.