By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Best way to link 2 tables

P: n/a
Ben
Right now I have 1 table. The first part is the first and last name
along with address etc. There is about 10-15 fields here. The second
part consists of times, penalties and if they enter this event or not.
With 30 events max times 3 thats 90 fields. Although within limits even
I know this is not the way to do it. I want to split the table into 2. 1
for the personal info and the other for the events.

Not sure what the best way to link them. I understand I need a common
field in both tables. The names won't work since people can listed more
than once. So I was thinking of using somthing like an autonumber for
the personal column than puting that number in the event table as a key
on both tables. I have been told this is not the best way to do it.

Most of the time only one person will be using it. There may come a time
when one other computer will be networked and 2 users will be running
it. One to do entries and the other to run the events but that is it. At
most we are talking 150 records.

I can not use design view because tables will be created in VB. I have
done some research in this but I have not really found someone to say
this is the best method.

Thanks.

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd create tables like this (these are Data Definition Language (DDL)
statements that can be sent to the db engine to create tables - you'll
have to change them according to your needs and the DDL syntax of the
db engine you'll be using). I haven't put in the Primary Key 'cuz for
Person tables that is sometimes tricky - depends on what you
know/expect of the data. You could try LastName and PostalCode - this
would require a NOT NULL for the PostalCode column definition.

CREATE TABLE Pesons (
PersonID IDENTITY(1,1) NOT NULL,
- -- Access uses COUNTER instead of IDENTITY(1,1)
FirstName VARCHAR(20),
LastName VARCHAR(30) NOT NULL,
Address VARCHAR(50),
City VARCHAR(40),
State CHAR(2),
PostalCode VARCHAR(10)
- -- ... other columns as needed ...
)

You'll need an Events table to hold the basic info about an event. A
good Primary Key for this tale would probably be the EventName and
EventStart date - but, that's up to you:

CREATE TABLE Events (
EventID IDENTITY(1,1) NOT NULL,
EventName VARCHAR(50) NOT NULL,
EventPlace VARCHAR(50) NOT NULL,
EventStart DATETIME NOT NULL,
EventEnd DATETIME NOT NULL,
- -- ... other columns as needed ...
PRIMARY KEY (EventName, EventStart)
)

Now you'll need a Participation table to hold info about which Persons
are attending which events & what they did (your Penalty, Time, etc.):

CREATE TABLE Participation (
EventID INTEGER NOT NULL ,
PersonID INTEGER NOT NULL ,
Penalty FLOAT ,
ElapsedTime FLOAT -- Time in Mins, Secs, ???
- -- ... other columns as needed ...

PRIMARY KEY (EventID, PersonID),
CONSTRAINT FK_PartEventID FOREIGN KEY (EventID) REFERENCES Events
ON DELETE CASCADE ,
CONSTRAINT FK_PartPersonID FOREIGN KEY (PersonID) REFERENCES Persons
ON DELETE CASCADE ,
)

The Primary Key prevents one person from being in the same event
multiple times. The Foreign Keys prevent events and persons from
being in the Participation table if they aren't in the Events and/or
Persons tables. The "ON DELETE CASCADE" means if the record in the
referenced table is deleted then the db engine should delete the
record in the Participation table that has the same EventID or
PersonID.

The linkage between tables would be like this (symbolic):

Persons - 1:m -> Participation <- m:1 - Events

The Participation table acts as a connection between the Persons and
Events, thus providing info on each persons participation in an event.

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4x3SYechKqOuFEgEQJ6mACfcYRTBBso0t6ErpoULBxz/E6OoPMAoL4F
mO7WhPrg/NGs4otx/5YgnPpA
=37CD
-----END PGP SIGNATURE-----
Ben wrote:
Right now I have 1 table. The first part is the first and last name
along with address etc. There is about 10-15 fields here. The second
part consists of times, penalties and if they enter this event or not.
With 30 events max times 3 thats 90 fields. Although within limits even
I know this is not the way to do it. I want to split the table into 2. 1
for the personal info and the other for the events.

Not sure what the best way to link them. I understand I need a common
field in both tables. The names won't work since people can listed more
than once. So I was thinking of using somthing like an autonumber for
the personal column than puting that number in the event table as a key
on both tables. I have been told this is not the best way to do it.

Most of the time only one person will be using it. There may come a time
when one other computer will be networked and 2 users will be running
it. One to do entries and the other to run the events but that is it. At
most we are talking 150 records.

I can not use design view because tables will be created in VB. I have
done some research in this but I have not really found someone to say
this is the best method.

Thanks.

Nov 12 '05 #2

P: n/a
Ben
Thanks. I think I can take it from there.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.