Multiple References in Database (Not sure what to call this!)

[MediaDoc]

Hi, not sure the right terminology here, so I'll try my best to explain the situation.

Database contains information on studies that scientists have completed (metadata).

tblMetaData contains the relevant info like:
MetaDataID (primary key, autonumber)
Author
Title
etc..
with a field called Publications - which I would like to be a list like:
3,7,41 (these numbers represent journal articles PublicationID that this study has published)

These will be linked to another table called tblPublications which contains relevant info like:
PublicationID (primary key, autonumber)
Author
Title, etc..
with a field called MetaData - which I would like to be a list like:
2,5,7 (these numbers represent the MetaData ID, that this publication has come from)

So now my quesiton.

These two tables are linked via Many-Many. How can I get a field to accept many different MetaDataID, or PublicationID numbers, and still be functional (in queries etc). I can see doing this with say 10 or 20 fields in each table (i.e. PublicationID#1, PubID#2 etc..) hoping I never have more than this. Doesnt seem like the most elegant solution though!

Hope this is clear.

Regards,

Matts

 

[Randomblink]

Eh?!

I am not sure I understand your question...
Let me try to rephrase in words I do understand...then you tell me if I am wrong or right...

You want to be able to have two tables...

Table:Alpha holds scientists Studies
Table:Bravo holds Publications

You want to be able to link Studies to Publications...
and vice versa...


Basically, you wanna be able to show that Study X is linked to Publications 1, 2, and 3

AND

Publication 2 is linked to Studies M, Q, and X

roughly...

Am I right...? Many to Many both ways...right?

IF this is correct...Here is how I go about the same thing...

I create Table:Alpha (we will call it: [tblStudies])
[tblStudies] has three fields (for this example)
stID - Autonumber field - Primary Key
stName - Text Field
stMisc - Whatever else you want...any number of fields...

I create Table:Bravo (we will call it: [tblPublications])
[tblPublications] has three fields (for this example)
pbID - Autonumber field - Primary Key
pbName - Text Field
pbMisc - other fields could include Issues number, etc...

THEN...here is the clincher...
I create ONE MORE TABLE...

I will call this one:
[tblLINK_tblStudies-tblPublications]
This one has TWO fields ONLY...(you could do more...but only for certain situations would you want to)

[tblLINK_tblStudies-tblPublications] has two fields
stID - Number Field - Primary Key
pbID - Number Field - Primary Key

Then, go into Relationships...
Link the fields appropriately...

[tblStudies] - stID <linked to> [tblLINK_tblStudies-tblPublications-stID]

and so on...

Now then, on your form...
You create a listbox that shows all of the Publications available...
Set up a function to run a sql code that INSERT INTO the Link table the stID from the Study you are entering...and the pbID from the listbox...

Does this make sense...?

I have a running example I can send you for a game database I am building...or I can send you a gif with my relationship layout if that would help...

Hope that helps...

 

[MediaDoc]

Thanks Randomblink,

Yes, you have the structure correct.

The third table is indeed the clincher! I would then add records to this table for each 'link' right?

For example.

stID - pbID
1 13
1 22
1 25

and vice versa
4 17
6 17
9 17 etc...

Thats great idea!

Relationships would be many-many.

Thanks very much for your insight!

Matts

 

[Pat Hartman]

Many-to-many relationships are implemented via a junction table.

tblMetaData
MetaDataId
etc.

tblPublications
PublicationID
etc.

tblPubXRef
MetaDataId
PublicationId

Storing multiple values in a single field is a violation of first normal form in addition to being almost impossible to use effectively unless you're into coding for the sake of coding.

 

[The_Doc_Man]

Access doesn't do Many-to-Many. But that has never stopped anyone before. The trick is called a linking table that has TWO one-to-many relationships, one to each of the key tables.

You have some things that give me pause in your design, though.

First, I don't understand MetaData unless all it does is report a study in which some number of folks have been active researchers. I.e. it is like a "projects" table. I am going to proceed as though that is what it meant.

Now, the other parts are easier to understand. You don't have enough tables here because you have other fields you listed that could lead you to grief, so I'll tell you what I would have done with this. This proposed structure is normalize with respect to persons, projects, and publications.

tblProjects - with an autonumber ID - a list of data about projects (studies) undertaken. Who funded it. Title of study. How much was the grant. Probably a big, ugly memo field describing the project charter. Maybe a file reference to a charter document or a hyperlink. But NOTHING about persons or publications. Only stuff about the project itself. If the project has definite start and end dates, they go here.

tblPublications - with an autonumer ID - a list of publications. Again, stuff about the publication. But NOTHING about persons or projects. Only stuff about the project itself. Like publisher, date, page reference, book reference (or magazine reference).

tblPersons - with an autonumber ID - a list of the persons who can participate in research and/or publication activities. Names, office phone numbers, other stuff that has to do with that person. But NOTHING about projects or publications.

Linking table #1: tblProjPers - contains fields for the project ID and person ID. (You could get fancy and put a field here if you wanted to show that person's role: Team leader, Researcher, Chief-Cook-and-Bottle-Washer, Flunky, ...) If there is an interest in knowing dates of participation, they would go here - in THIS table.

This table has the relationship: one (Person) to many (ProjPers). You have one entry in this table for each person who participated in a given project. It also has the relationship one (Project) to many (ProjPers).

To use it in reports, it depends on what your report or query is seeking. You can JOIN this linking table to the project and to the person tables, but make THIS table the driver for your report. Then if you group by tblProjPers![PersID], you can list the projects in which a person participated. Group on ...![ProjID] instead and you'll get the list of persons who worked on a project.

Linking table #2: tblPublPers - contains fields for the publication ID and person ID. Again, many options. Could include a field to show function: Senior Author, Contributor, Spell-checker for Techie Words, Comma-Cleanser, ...

This table has the relationship: one (Person) to many (PublPers). Also one (Publication) to many (PublPers). Use it grouped by publication to see authorship lists. Use it grouped by Persons to see what publications each person has contributed.

Linking table #3: (Optional, if this happens) tblPublProj - contains fields for the project ID and publication ID. Again, options as appropriate. Used to show how many projects were covered in a publication AND/OR how many publications you got from a single project.

This table has the relationships: One (Publ) to many (PublProj) and One (Proj) to many (PublProj).

Now, the indexes:

Of course, the autonumberID fields in each of the Publication, Project, and Person tables are primary keys (indexed, no dup). You can certainly make indexes on other fields, probably (indexed, dup ok). For Person table, names are often indexed. For publications, the magazine name or book name is usually indexed. Don't know what else you might index for projects except maybe funding source? But that's your call.

In the three linking tables, each of the ID-linking fields can be (indexed, dup ok). These tables WILL NOT have (or really need) a primary key. Very rarely will you need any other indexes on linking tables.

 

[MediaDoc]

FYI, MetaData files (in my case anyways) contains descriptions about the study, but not any data the study generates. Does not affect all of your very helpful suggestions.

Im from a web background, and this is for an online searchable database, so this 'conceptual' help is indeed very valuable to me.

Many many thanks.

I like the idea of pulling out authors. This will allow easy entry of multiple authors for each publicaiton, or study. (I had previously had a memo field holding all that crap). A bit more time upfront to setup and enter all the data, but it will pay off.


P.S. Access told me if I dont put a primary key in my linking table, it can't set up relationships (access 2000). Didnt stop me from doing it though.

Next challange, .ASP the whole thing!

Cheers,

Matts