|
| |
|
|
abbeyvet
Posts: 5094
From: Kilkenny Ireland
Status: offline
 |
Database design - 7/14/2003 14:36:01
I am so rubbish at thinking out the structure of databases. [:' (]
This is what I want.
- There are a number of named courses.
- Each course will be run multiple times
- Each course comprises 6 one-day modules, usually a day a week for 6 weeks.
- Courses can take place in a number of different venues
- Price can vary for any given course depending on venue
- Different courses could be running simultaneously in different venues, or the same venue.
- I am calling each instance of a course an Event. Each event has a course leader
These are my tables so far but I am not really sure they make sense or are the most efficient way of organising it all. Am I over complicating it? Oversimpyfying it?
Courses
--------
courseID (primary Key)
cname
cadmin
Leaders
-------
LeaderID (Primary Key)
lname
email
phone
address
Venues
-------
vname (Primary Key)
contact
address
phone
web
email
Events
-------
eventID (Primary Key)
cname
vname
lname
date1
date2
date3
date4
date5
date6
price
In the events table, should I be using the primary keys from the other tables rather than just vname, lname, cname etc?
Any input GREATLY appreciated!
_____________________________
Katherine
:: InKK Design :: InKK Domains
|
|
|
|
bobby
Posts: 11828
Joined: 8/15/1969
From: Seattle WA USA
Status: offline
|
RE: Database design - 7/14/2003 17:02:55
quote:
should I be using the primary keys from the other tables
Yes.
You need to try to make sure that your tables all have a 1:M (one to many) or M:1 relationship... IOW, for each (1) record in tableA there are many (M) records in tableB that correspond.
Basically in tableB you use the Primary Key in tableA to relate back to that table...
In the event that you have a M:M (many to many) relationship... where there are multiple references relating back and forth... you need a " junction" table that has a 1:M relationship back to each of the M:M tables...
Your Events table might be this " junction" if you used the primary keys from the other tables, rather than the non key fields...
It' s also a good idea to break up things like " contacts" and " address" into FirstName, LastName, and Address, City, State, Zip so that you can have more flexibility over the data and can access by " city" rather than the entire " address" field...
Also, you can order by last name or first, zip code, state, etc.
Did that make sense?
Relational DB design is a tough concept to grasp, let alone master or instruct. I' ve taken two workshops and read a couple books on the subject, and it' s still hazy. 
Remeber not to duplicate data in tables, unless it' s a primary key... because you can use the PK to retrieve the ancilary data via ASP or a stored procedure (query) much easier and faster, and without breaking relationships.
I won' t even go into " normalizing" or " rationalizing" the data... that' s a workshop in itself. And typically, unless you' re really getting complicated, will work itself out.
_____________________________
Talk to your kittens about catnip, or someone else will.
| |
|
|
|
J-man
Posts: 936
From: Canada
Status: offline
|
RE: Database design - 7/14/2003 17:38:50
my thoughts...
You should definitely be using the primary keys in the Events table. Also when you set up the relationships in the relationships window make sure you select " Enforce Referential Integrity" for each of the relationships. Other than that the setup doesn' t seem bad...the goal is to store all of the data but not repeat any data. Hope that helps.
| |
|
|
|
J-man
Posts: 936
From: Canada
Status: offline
|
RE: Database design - 7/15/2003 10:00:19
abbeyvet, are you using a server-based DBMS for the back-end?.....How are you securing the database?
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 