[GENERAL] Schema design / joins
От | E H |
---|---|
Тема | [GENERAL] Schema design / joins |
Дата | |
Msg-id | 7027958f-bbe6-4571-9659-71dc4b317344@e2g2000yqn.googlegroups.com обсуждение исходный текст |
Ответы |
Re: [GENERAL] Schema design / joins
|
Список | pgsql-sql |
Hi, This is more of a general schema design, any advice is much appreciated. I have a Organization table. Nearly every other table in the schema is related to this Org table in some way. So, some tables may be 3 or 4 tables 'away' from the Org table. In order to filter by the org_id, I need to join a bunch(?3-6) of tables Simple example below, TeamFees belong to a Team, which belongs to a Season, which belong to an Org. In order to get all the TeamFees that belong to a given Org, I need to join all the tables which isn't a big deal, but I'm just wonder if putting an extra 'org_id' on Team fees would help anything... ** Is it a bad idea to put an extra FK 'org_id' on the TeamFees table to avoid all the joins? ** What about putting an 'org_id' on every table? (it seems somewhat redundant/unnecessary to me) I've never had any formal education in rdbms, but from what I can gather, foreign keys are meant to ensure data consistency, not reduce the number of joins required. Although, it sure seams like it would simplify the queries if I stuck extra 'org_id' columns in certain places. I don't have any particular reason that I'm trying to avoid joins -- I'm just wondering if there is something simpler or if 'thats just how it is.' I would really, really appreciate any suggestions from folks with rdbms schema design experience! Thanks! __Orgs__idname __Seasons__idorg_id fk(orgs.id)name __Teams__idseason_id fk(seasons.id)name __TeamFees__idteam_id fk(teams.id)*org_id <--- (?put extra fk here to avoid many joins?)
В списке pgsql-sql по дате отправления: