Basic Database Design

[JCAccess]

Hey all, I'm a newbie studying info systems. Just a simple question that everyone probably knows.
To avoid data redundancy, you would only want the same field to be listed twice in a database in order to link tables? Or is there any other reason to have the same field listed more than twice? I'm dealing with a compound key table to make a many to m any relationship

[Scott Price]

Hey all, I'm a newbie studying info systems. Just a simple question that everyone probably knows.
To avoid data redundancy, you would only want the same field to be listed twice in a database in order to link tables? Or is there any other reason to have the same field listed more than twice? I'm dealing with a compound key table to make a many to m any relationship

In many to many relationships you need three tables. This breaks it down into two one to many relationships. In this case the middle table acts as a linking table in which you have 3 basic fields: A primary key and two foreign keys.

I personally do not like composite primary keys (i.e. using more than one field to create the unique value required for a primary key). They are too complicated for general consumption and are tricky to refer to when using them as foreign key references in other tables. Access provides the Autonumber data type for the express purpose of creating a unique easily digestible primary key, and when used correctly it greatly simplifies relationships.

Have a look also at this tutorial: Database Normalisation and Table Structures

Regards,
Scott

[Scott Price]

And to answer your question more precisely: No, there is no reason to limit the number of times a field can show up in relationships. When linking between tables the primary key from one table is used as a foreign key in another table, and there are really no artificially imposed limits on how many times one table can be linked to other tables.

Regards,
Scott