Composite Keys refering to multiple tables

[ApacheTech]

Is it possible to set up a composite key for a table that can be referenced correctly in more than one table.

For instance, I have two tables with unique sets of data, tblStudents and tblTeachers. In each table I have a Notes field, and as there can be more than one note for each entity, this is set up as a separate table using a composite key "NoteID + StudentID" or "NoteID + TeacherID".

In this example, I would need two separate tables, tblStudentNotes and tblTeacherNotes. Is it possible to merge these two tables to tblNotes and then have the following fields:


NoteID
TableID
RecordID
Notes


With the three ID fields as a composite key?

The theory sounds easy enough, but how do I reference the table name/id into TableID?

[james438]

Although this sounds like an interesting challenge is it really that important if you are only referencing two tables for your TableID? If that is the case just assign the number 1 to tblStudentNotes and the number 2 to tblTeacherNotes. There won't be a need to set up a table just to store the names of the two tables. If you wanted you could forgo the TableID entirely if you wanted ;).

I'm not saying that it is a bad question at all. It is a good one, but it sounds like you may be over thinking or over complicating this a bit.

[james438]

Just for fun I tried it out and here's what I came up with.

I created 4 tables:

TableID

ID
Name


tblStudents

ID
Student


tblTeachers

ID
Teacher


tblNotes

ID
TableID
RecordID
Notes

Sample data was inserted into each table. For tblNotes I assume that RecordID relates to either the Teacher's ID or the Student's ID. If I want to display the Notes from the Student Barry Borshki, his Name and the Name of the table Student's name came from the query would look like this:


SELECT tblStudents.Student, tblNotes.Notes, TableID.Name
FROM tblStudents, tblNotes, TableID
WHERE tblNotes.TableID=TableID.ID
AND tblNotes.RecordID=tblStudents.ID
AND tblStudents.ID=2
AND TableID.ID=1

You can use LEFT JOIN, RIGHT JOIN, OUTER JOIN, but I have not learned them and the above query works just fine and is easier for me to understand :).

[ApacheTech]

Cheers for that. I need to set this up in Access so Notes would need to be a sub form referenced from each table.

The data shown above is just a sample set. In the actual database, I have five tables that need both a Notes field and an imageFileNames field. At the moment I have 15 tables to achieve this part alone, not to mention the other normalisation tables. I thought there must be a better way to do this.

I certainly don't mind having so many tables if that's the proper way to do it.

[james438]

If I am understanding you correctly it sounds like having a table just for table names would be a good idea in your case. If you had only two table names to keep track of then I figured it was kinda silly to have a table for them.

Am I correct in understanding that you have 15+ tables and you are trying to prune the number to something more manageable? I do not have any experience with Access, but I imagine it is similar to MySQL. Was my post helpful in your case?

[ApacheTech]

Yeah.

I'll give you the full table list.

Main Tables:
tblVenues
tblConcertVenues
tblDanceClubs
tblFestivals
tblLinks
tblMusicClubs

Relational Tables:
relConcertVenueImageFileNames
relDanceClubImageFileNames
relFestivalImageFileNames
relLinkImageFileNames
relMusicClubImageFileNames

relConcertVenueNotes
relDanceClubNotes
relFestivalNotes
relLinkNotes
relMusicClubNotes

System Tables:
sysLinkCatagories
sysUKCounties
sysUKCountries
sysMonthlyFrequencies
sysWeekDays

This all works fine and I have no problems with the database functionality, I just want to make it more elegant and I'm not sure if this is the best structure for such a database. I'm using this database with ASP.NET to create a database driven website so I need it as optimal as possible.

And before anyone says it, no I cannot use MySQL, as I need a front end of the database which is user friendly, Access is still, after 20 odd year the best front end a database can use. I'm using ASP.NET because I can't find a single webhost that allows Access databases on a PHP site. I've searched for months for a decent host but there is nothing at all so far as I can see.

P.S. I'm using the Hungarian Naming Method because that's what I'm used to when programming. Each to their own I suppose. :p

[james438]

No need to defend yourself for using Access. Most questions here deal with MySQL because it is the most commonly used language for web designers. I obviously fall into that category as well. From what I have seen Access is really good too, but a little different from MySQL and is the language used by many large businesses.

If my MySQL answers are helpful then I am happy to help :).

Kinda off topic, but what is your business? Do you have a link to your business website? It is often interesting for us to see the website that the question relates to.

[ApacheTech]

I'm redesigning my dad's website. It's a UK based national magazine, listing Folk and Roots music events throughout the UK. As you can see from the site, it's in great need of SEO.

http://www.folkorbit.co.uk

[ApacheTech]

I can't seem to edit this thread to resolve it, but I'll give the resolution details here to finalise the thread and hopefully a mod can set the thread to resolved.

With regards to the three field composite key, this is entirely possible. I have moved over to using MySQL and PHP instead of ASP.NET and Access, only because I haven't been able to find a single web host that offers suitable support for ASP.NET 4.0.

My final database structure can be seen in the attachment. I have two tables, adverts and websites that use the three field composite keys.


http://phpsandbox.x10.mx/img/site/database_relationship_framework.png

However, there is a caveat. This is difficult to set up in Access because of the way it handles NOT NULL fields and lookups. You need to manually enter and retrieve the `table` field. I'm also finding it difficult to formulate SQL SELECT statements to gather all the info. I've ended up adding the extra fields to the array after the initial SELECT.




<?php

$links = $db->query('SELECT L.*, C.name AS category FROM links AS L INNER JOIN categories AS C ON C.id = L.category_id');

foreach($links as $id=>$link) {
$websites = $db->where('table_ref',$_SESSION['PAGE'])->where('record_id',$link['id'])->get('websites');
$adverts = $db->where('table_ref',$_SESSION['PAGE'])->where('record_id',$link['id'])->get('adverts');
$links[$id]['websites'] = array();
$links[$id]['adverts'] = array();

foreach($websites as $key=>$value) {
array_push($links[$id]['websites'], $websites[$key]['url']);
}

foreach($adverts as $key=>$name) {
array_push($links[$id]['adverts'], $websites[$key]['name']);
}
}

?>


This seems to be working fine and I've now adapted the above code into a reusable function to dynamically add any three-key tables to an array using input paramaters.

For now, this topic is resolved.

However, there's more action over at: http://www.dynamicdrive.com/forums/showthread.php?t=69656