|
Database design question!!!
|
View this Thread in Original format
| Lunar Phase 7 |
I have resolved a *.* relationship with a junction table in access (to check my database schema before coding in mysql).
In my mind this junction table which links many football teams to many differnt seasons would also serve well to resolve other issues (for example managers managing many teams over many seasons)
Is it viable to use a junction table as a parent table that other child tables will reference? What could be the pottential issues of doing this? I intend to enforce referntial integrity with cascade update.
If it is viable would you suggest assigning a new primary key in the junction table for simplicity, or would it be best to use a superkey comprising of the primary keys from team and season?
Hope this isn't too confusing.
I am really dumb at db's. |
|
|
| malek |
is this for school? if yes then
yes for a junction table and cascade updates
and yes use a superkey. a unique ID for that table could be helpful but not necessary, and don't make it a PK.
in the real world things are a bit different to keep a certain performance level. |
|
|
| Lunar Phase 7 |
| quote: | Originally posted by malek
is this for school? if yes then
yes for a junction table and cascade updates
and yes use a superkey. a unique ID for that table could be helpful but not necessary, and don't make it a PK.
in the real world things are a bit different to keep a certain performance level. |
I realise the primary key won't be neccessary but if lots of tables are refernceing this then it would be far easier to reference one column as appose to two would it not?
Also are you SURE that referenceing a junction table is viable?
Thanks,
Lunar. |
|
|
| Joss Weatherby |
| quote: | Originally posted by malek
in the real world things are a bit different to keep a certain performance level. |
In the real world I hope it wouldn't be Access either.
I just finished a project at my job for the transit authority in one of the larger counties in the US and their entire customer database with hundreds of thousands of records was all in once Access database... :( Access '97! |
|
|
| Lunar Phase 7 |
| quote: | Originally posted by Joss Weatherby
In the real world I hope it wouldn't be Access either.
I just finished a project at my job for the transit authority in one of the larger counties in the US and their entire customer database with hundreds of thousands of records was all in once Access database... :( Access '97! |
It won't be in access though, the final version will be in MySQL.
If you have just completed such a vast project could you please enlighten me as to whether using a relation that resolves a *.* relationship can be referenced by a different referencing relation?
I hope to god it can because if so it solves all of my issues.
Would help if some of my tutors (not all, just the one that runs this module) actually knew this stuff.
RDBMS's is ing hard, yo. |
|
|
| malek |
| quote: | Originally posted by Joss Weatherby
In the real world I hope it wouldn't be Access either.
I just finished a project at my job for the transit authority in one of the larger counties in the US and their entire customer database with hundreds of thousands of records was all in once Access database... :( Access '97! |
that happens too :( |
|
|
| malek |
| quote: | Originally posted by Lunar Phase 7
I realise the primary key won't be neccessary but if lots of tables are refernceing this then it would be far easier to reference one column as appose to two would it not?
Also are you SURE that referenceing a junction table is viable?
Thanks,
Lunar. |
wait i'm not sure i'm following you.
post a diagram if you can, or give a detailed example. |
|
|
| Lunar Phase 7 |
| quote: | Originally posted by malek
wait i'm not sure i'm following you.
post a diagram if you can, or give a detailed example. |
Okay,
I am resolving a *.* relationship between team and season with a junction table teamseason.
This could also in my mind help solve other issues such as a manager looking after different teams in different seasons.
manager to season relationship would be *.*
manager to team is 1.*
If i could link manager table to the teamseason table would it not eliminate the need for a seasonmanager junction table and also represent the 1.* relationship between manager and team?
BASICALLY. Can you use reference a junction table with tables other than those upon which it's purpose is to solve a *.* situation? Or will I need loads of pissy complex junction tables for every ing thing.
RDBMS sucks.
Thanks guys,
Lunar. |
|
|
| Joss Weatherby |
| quote: | Originally posted by Lunar Phase 7
Okay,
I am resolving a *.* relationship between team and season with a junction table teamseason.
This could also in my mind help solve other issues such as a manager looking after different teams in different seasons.
manager to season relationship would be *.*
manager to team is 1.*
If i could link manager table to the teamseason table would it not eliminate the need for a seasonmanager junction table and also represent the 1.* relationship between manager and team?
BASICALLY. Can you use reference a junction table with tables other than those upon which it's purpose is to solve a *.* situation? Or will I need loads of pissy complex junction tables for every ing thing.
RDBMS sucks.
Thanks guys,
Lunar. |
You could do it either way but I always find that putting too much information inside a relationship table (what you call a junction table) can lead to headaches. |
|
|
| Lunar Phase 7 |
| quote: | Originally posted by Joss Weatherby
You could do it either way but I always find that putting too much information inside a relationship table (what you call a junction table) can lead to headaches. |
Mind just elaborating why? |
|
|
| Joss Weatherby |
| quote: | Originally posted by Lunar Phase 7
Mind just elaborating why? |
You said that you want to put a relationship to the manager in the table that relates teams to seasons, well what if the team has multiple managers over the course of one season? If you want to track the history of the managers then you would have multiple relationship rows. |
|
|
| Lunar Phase 7 |
| quote: | Originally posted by Joss Weatherby
You said that you want to put a relationship to the manager in the table that relates teams to seasons, well what if the team has multiple managers over the course of one season? If you want to track the history of the managers then you would have multiple relationship rows. |
I see, thank that is not of concern for me. One team = one manager per season, usually for many many seasons.
Damn database desing is hard!
What is your typical schema like in the real world?
I'd be interested to see how real issues are dealt with given the complexity of my hypothetical scenario. |
|
|
|
|
