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]
