Primary Keys

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 18 Jan 2006 09:05 GMT

Right in my Tables i have set School Name as the Primary Key, each week
details from each school (55 in total) are retunred and then entered in
to the database. I have set up a combo box with all the school names.
Will there be a problem with having the School Name as the primary
key??? I think there might be as i will be using same 55school every
week without fail but each school has different details stored about
no. meals, no tickets etc sold every week. What av got is:

table 1 : School

School Name * (Primary key)
Weekending
No meals
No Days

table 2: Meals

Meal Id* (Primary Key)
School Name
No Tickets
Tickets returned
Tickets supplied
Milk sold
SW MOW

should i possily have a unique id like School Id for the primary key in
table 1???

fie

Reply to this Message

chriske911 - 18 Jan 2006 09:31 GMT

> Right in my Tables i have set School Name as the Primary Key, each week
> details from each school (55 in total) are retunred and then entered in
[quoted text clipped - 25 lines]
>
> fie

no, you can use anything you like as a primary key
as long as each entry is unique all will be fine
if you are dependant on an external source file it is the easiest way
to link data

keep in mind that the linked field should always be identical to your
primary ID field value
to check this I use an unmatched query after an import of data

grtz

Reply to this Message

Allan Murphy - 18 Jan 2006 10:01 GMT

I would use an unique ID for School to avoid errors in entering the school
name, also in the future would the school name change slightly?.

Signature

Allan Murphy
Email: allanmurphy@unwired.com.au

> > Right in my Tables i have set School Name as the Primary Key, each week
> > details from each school (55 in total) are retunred and then entered in
[quoted text clipped - 36 lines]
>
> grtz

Reply to this Message

Fie - 18 Jan 2006 10:14 GMT

there is a possibily that it might...!! I thought it would be best to
have an unique school id... just made it pretty simple am lot happier
with that and its more logical cheers :-)

Reply to this Message

Klatuu - 18 Jan 2006 13:28 GMT

Using the school name as the primary key is not your best database design
technique. What happens when a school changes the name? You would have to
change that name in every row in every related table. This is exactly what
autonumber fields are for. You use the autonumber field as the primary key.
All the child tables should be related using the autonumber primary key, not
the school name. The autonumber field never changes and changing the school
name has no effect on the relationships to the child table. In addition,
using the school name as a primary key degrades performance and wastes disk
space.

> > Right in my Tables i have set School Name as the Primary Key, each week
> > details from each school (55 in total) are retunred and then entered in
[quoted text clipped - 36 lines]
>
> grtz

Reply to this Message

Brendan Reynolds - 18 Jan 2006 13:42 GMT

Here in Ireland, schools have a 'Roll Number' (actually an alpha-numeric
code) assigned to them by the Dept. of Education. Do schools in the UK have
anything like that? If so, it would probably be a better choice than the
school name.

Signature

Brendan Reynolds
Access MVP

> Right in my Tables i have set School Name as the Primary Key, each week
> details from each school (55 in total) are retunred and then entered in
[quoted text clipped - 25 lines]
>
> fie

Reply to this Message

Fie - 18 Jan 2006 14:59 GMT

tis oklay dokaly... av sorted out my prob. using combination of
schoolname and date to produce school
code and its working well...

thanks guys

Reply to this Message