Many to Many with no fields

[PTKen]

I have a situation that appears to be a many to many relationship, but there would be no fields in one of the files except for the primary key. In other words, the only reason for having the second file is to generate a unique index (incremental numbering for CD-ROMs for example). Then I would have a join file that has a relation to the primary key for both files as well as the field(s) to enter.

My question is whether is it common to have a file in a many to many relationship that contains nothing but the primary key or whether I need to ask a different question about my design? I can give more details about my tables if necessary.

Thanks in advance.

Ken

[LiveOak]

It would be uncommon for me to have a file with one field, as I always have some housekeeping fields (creation date, creation time, modification date, modification time, Contant = 1, etc.). It's hard to know if this is the correct design for your situation without more information about what you are doing. If you are serial numbering something, it is somewhat unusual not to need to record some data related uniquely to each serial numbered item.

-bd

[Kurt Knippel]

If in this situation your "Primary Key" is the only thing of relevance that the DB creates, it is fine having only one field. However, just to be clear, this is NOT part of a many-to-many relationship. It cannot be since there is only 1 field, it can at most relate to one DB at a time.

It may be related to more than one other DB, but this does not make a many-to-many relationship. This just makes a DB with many one-to-[zero, one, many, etc] relationships

[PTKen]

If in this situation your "Primary Key" is the only thing of relevance that the DB creates, it is fine having only one field. However, just to be clear, this is NOT part of a many-to-many relationship. It cannot be since there is only 1 field, it can at most relate to one DB at a time.

It may be related to more than one other DB, but this does not make a many-to-many relationship. This just makes a DB with many one-to-[zero, one, many, etc] relationships

Thanks for your replies. I think I need to clarify. I do in fact have more than one item that is being tracked, but when I said there was only one item in the second database, I was speaking about 'after' setting up a join file. Here's what I'm specifically trying to do:

File "JobItem" contains these fields:

Workorder_Num (Primary Key)

Date

Technician

...etc...

File "Archive_Item" contains these fields:

Archive_ID (Primary Key which is same as name of DVD, CD, etc)

Folder_Name

The many to many relationship is because all files for one workorder number may be saved onto multiple DVDs, CD, etc. and each one DVD may have files saved from multiple workorders. So I have broken the files into three to create a join file:

File "JobItem" contains these fields:

Workorder_Num (Primary Key)

Date

Technician

...etc...

File "Archive_Item" contains this ONE field:

Archive_ID (Primary Key which is same as name of DVD, CD, etc)

File "Workorder_Archive_Join" contains these fields:

Workorder_Num (Foreign Key)

Archive_ID (Foreign Key)

Folder_Name

In this setup I am left with only the primary key in the archive file. In the join file I would enter one record for each folder that is backed up, putting the Archive_ID (i.e. DVD name) and Workorder that it is related to. I think I'll be needing calculated fields as well, but I didn't get that far yet because I've never had just one field in a database before and I felt stumped. Also, I have no primary key in my join file. Is this a problem? I don't see it as one, but I can add a primary key that just increments by one if it's recommended.

So, any suggestions on how to achieve this? Suggestions on how to set up the calculated fields that I will need?

All that I am trying to track is if I enter a workorder number, I can find all DVD(s), CD(s), etc. that contain files for that job and if I enter an Archive_ID, I can see all the jobs that were on that disk (useful if a DVD becomes unreadable).

Thanks for any advice you can give!

Ken