understanding relationships in database (1 Viewer)

[sueviolet]

Hi there


I have just received a database (from which I need to extract selected data and import into mine). It appears to consist of 3 databases linked together: one just has forms (data entry database), the second tables which contain the data and then a database just for lookup tables.


The part of the database I am interested in, is the database which actually contains the data.

The thing is, None of the tables have any fields with unique id's (though there are fields which are specified as primary keys) - there are no relationships defined between any of the tables in the relationship window.


All I have to go on to put together queries it a data model diagram. I feel there is something I am not getting - as this database was designed by people with more expertise than me in this area. Could someone please help me understand how you can design a database with no unique ids.

i need to put together queries, but the relationships between the tables are a mystery to me. (it is my understanding that relationships work by matching data in key fields)


I can post the database to take a look at if needed.


Thanks for your help!

 

[pono1]

I have read your post two and half times -- but can't quite get it, so to be sure we're communicating, here's a vocabulary check: a primary key is a field used to uniquely identify records in a table.

And, yes, matching up certain fields between tables -- primary keys and foreign keys -- is the seed of a relationship in relational database speak (thanks to some old coot at Cal Berkeley, funded by IBM, who devised the original rules and recommendations in the early seventies).

So when you say there are no "unique ids," do you mean there is no exchange of primary key fields between tables?

Here, for example, would be three simple tables that you can merge together -- meaningfully -- using a query.

TblVoyages
Fields: VoyID (Primary), ContainerID, DepartDate, ArriveDate

TblContainers
Fields: ContainerID (Primary), Length, ClientID

TblClients
Fields: ClientID (Primary), ClientName

Notice how TblVoyages includes one "redundant" field (ContainerID), called a foreign key (FK), that matches the primary key (PK) of TblContainers. And notice that TblContainers includes a "redundant" field (ClientID), another FK, that matches the PK of TblClients.

Containers are owned by clients who pay for them to be shipped on voyages. There's a real world relationship there. And it's mimicked in the database by the matching fields in the tables, with TblContainers directly linked up with TblVoyages -- and TblClients, through TblContainers, also related to TblVoyages.

Given your three mystery tables, a key question to ask yourself: What's the real world relationship that these tables mimic? And then take a hard look at the fields: are the primary keys in the tables duplicated in another table? (They do not need to have the same name -- though, of course, that would make it easier.) If so, bring together all tables at your Access query screen and simply drag and drop primary keys onto foreign keys to consumate the relationships; then sit back, experimenting with the grid, clicking and watching as the queries untangle and re-assemble the data to create new recordsets.

If there are no logically related fields (PKs and FKs), then I'm not sure what to say, except that you may as well have the information on three separate pieces of paper, which is not a pleasant thought.

Regards,
Tim