Bruce,
>From your one of your other threads, your object hierarchy looks like:
state
university name
school name (school of medicine/engineering/accounting/etc..)
dept name
course/class name
class section
class day/time
instructor
To me, it seems as though you have already thought through your design. In
the database, each "child" element should only need to keep up with the ID
of its "parent" element. You should not need to include the IDs to
"grandparents" or "great-grandparents" as, you already said, that would be
redundant.
So... I hear the wheels grinding away already... can you have the same
School name, let's say... "Engineering", belong to more than one
University and still maintain your normalization? Of course but that all
depends on how "normalized" are need to be. This is the point where
database design theory smacks head-first into application performance. In
most applications it will be perfectly fine to have a table like
CREATE TABLE School (
ID int auto_increment primary key
, Name varchar(30) not null default 'unkown'
, University_ID int not null default 0
, UNIQUE KEY (University_Id, Name)
)
This table will permit a University to have one if its "School"s to have
the same name as a School in another University, but you cannot define the
same School name more than once for any one University. Yes, it is not
"absolutely normalized" as the same School name will appear more than once
in your data but that's not always a "bad" thing.
Taking normalization to the next level, you would need to keep another
table of just SchoolNames and replace the Name column in the table above
with the ID of the SchoolName table. *However* this additional step of
normalization will require you to JOIN an additional table EACH TIME you
want to see the name of any particular School. The trade off here is
storage space vs. application performance. IMHO, I would not make that
additional normalization unless I was absolutely pressed for space. The
space you save would also depend on the cardinality of your data. If you
have mostly unique names, you will save far less space than if you had many
duplicates. Under certain conditions you would actually end up taking more
room in the more normalized design than you would with the less normalized
form.
The same situation applies to each "child" in your object tree. I am sure
there are several thousand "101" sectioned classes nationwide however the
space saved by converting that relatively short string into a 4-byte number
(int) and normalizing it would be minimal if any. But the performance hit
you would take each time you had to JOIN the SectionName table to the
Section table every time you needed to use that data would be significant.
In your sample, you are trying to model your Course object. You should only
need the Course's name and the Department_ID to uniquely identify any
course in your database. You should not need to store the state,
university, or school IDs in the Course table as you can get to those IDs
by navigating "up" your hierarchy tree. (INNER JOINing the "parents" to
their "children")
I hope this made sense as I notice that you have several prior threads on
related topics and I am not sure how well you understand these topics
yet... If anything I said does not make sense, _please_ just ask. There is
no foolish question except the one never spoken.
Warmest regards,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"bruce"
<[EMAIL PROTECTED] To: <[EMAIL PROTECTED]>
ink.net> cc: <[EMAIL PROTECTED]>
Fax to:
07/08/2004 01:42 Subject: RE: table layout structure
PM
Please respond to
bedouglas
shawn...
thanks for the response...
here's my basic situation. i'm creating an app that's going to have
university class schedules from a number of universities...
as i get down to the table that will house the actual class schedule
information, i'm looking at what informational fields need to be defined.
some of the fields will not exist in various universities given the way the
university defines their output. (the information is being extracted from
the university web site).
that said, i need to think about what relationship the fields should have
to
each other (and to other table information) and how i deal with potential
duplicates of various information field data.
current table structure...
create table courseTBL( name varchar(50) not null default '',
universityID int(10) not null,
-- schoolID int(10),
section varchar(10) null default '',
deptID int(10) not null default '',
instructorID int(10) not null default 'TBA',
semesterID int(10) not null default 'TBA',
day varchar(20) null default 'TBA',
time varchar(20) null default 'TBA',
ID int(10) not null auto_increment,
primary key (ID),
unique key (name, universityID, deptID)
-- unique key (name, section, deptID)
)type =bdb;
here is the current structure i'm considering.. however, it is possible
that
different universities might have the same classname, deptID for a given
course. so i need something to add to the mix so to speak that will allow
me
to not have duplicate information for a given school... in this case,
universityID is maintained/derived from the universityTBL...
however, using the universityID requires that i now duplicate data within
the system... which i want to keep to a minimum!!!
thanks..
-bruce
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 08, 2004 10:13 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: table layout structure
Bruce,
It all depends on what you are storing in foo1 and foo2.....
For a one-to-many relationship, you need to have that ID column in your
child table to reference the parent record.
The "classic" example of a one-to-many relationship is Companies to
Employees. In the real world there are some cases where one person "could"
work for more than one company at a time but not for this example....
CREATE TABLE Company (
ID int auto_increment
, Name varchar(40) primary key
,Key(ID)
);
CREATE TABLE Employee (
ID int auto_increment
, Company_ID int
, Name varchar(40) primary key
,KEY(ID)
);
INSERT Company (Name) values ('ABC Corp'),('XYZ Supply Company'),('Some
Other Company');
INSERT Employee (Name, Company_ID) Value ('J Smith',1), ('K Jones',1), ('S
Serpentine', 2), ('B Dog', 2), ('S Pony',3);
This results in the following data:
SELECT* FROM Company;
+----+--------------------+
| ID | Name |
+----+--------------------+
| 1 | ABC Corp |
| 2 | XYZ Supply Company |
| 3 | Some Other Company |
+----+--------------------+
3 rows in set (0.04 sec)
SELECT * FROM Employee;
+----+------------+--------------+
| ID | Company_ID | Name |
+----+------------+--------------+
| 1 | 1 | J Smith |
| 2 | 1 | K Jones |
| 3 | 2 | S Serpentine |
| 4 | 2 | B Dog |
| 5 | 3 | S Pony |
+----+------------+--------------+
5 rows in set (0.00 sec)
This shows that J Smith and K Jones both work for ABC Corp. The value in
the Company_Id field must exist in both tables to form that relationship.
What exactly are you trying to model?
In regards to your question about unique ID numbers between tables, you may
be looking for the term "foreign key" but I won't know if that applies to
you until I understand your situation better.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"bruce"
<[EMAIL PROTECTED] To:
<[EMAIL PROTECTED]>
ink.net> cc:
Fax to:
07/08/2004 12:49 Subject: table layout
structure
PM
Please respond to
bedouglas
hi...
i have an issue/prob and i'm trying to figure out the best approach...
i have multiple tables foo1, foo2
foo1: foo2:
- name - name
- id - foo1_id
- id
- unique key (id, name, foo1_id)
my question: does mysql provide a way to do a unique key on table items
between multiple tables, or do i need to essentially create my logic/code
to
handle/ensure the relationships myself. im my above exmaple, i'd rather not
have to have "foo1_id" in table foo2 as it is a duplicate of "foo1.id"....
i'd really like to be able to do something like
"unique key (id, name, foo1.id)"<<<<<
any thoughts/comments/criticisms...
thanks
-bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
