What Keys to use for Many-to-Many-to-Many?

Databases
1

I haven’t made my mind up yet, but am thinking that Articles on my website will follow this URL-structure…


[INDENT][COLOR="#0000FF"]w[B][/B]ww.mysite.com/finance/accounting/taxes/why-you-should-hire-a-cpa[/COLOR][/INDENT]
[INDENT][COLOR="#0000FF"]w[B][/B]ww.mysite.com/operations/manufacturing/production/is-just-in-time-the-way-to-go[/COLOR][/INDENT]
[INDENT][COLOR="#0000FF"]w[B][/B]ww.mysite.com/legal/type/litigation/does-suing-copyright-infringers-make-sense[/COLOR][/INDENT]

Here are the Entities and Relationships involved…

Entities:

  • Category
  • Dimension
  • Sub-Category
  • Article

Tables:

CATEGORY

  • id
  • name

DIMENSION

  • id
  • name

SUBCATEGORY

  • id
  • name

ARTICLE

  • id
  • slug
  • author
  • written_on
  • heading
  • body

Relationships:
CATEGORY -||-----|<- CATEGORY_DIMENSION ->|-------||- DIMENSION

DIMENSION -||----------|<- SUBCATEGORY

CATEGORY_DIMENSION -||-----------|<- SUBCATEGORY

SUBCATEGORY -||-----------|<- SUBCATEGORY_ARTICLE ->|------------||- ARTICLE

Questions:
1.) Is the second Relationship needed?

2.) For the CATEGORY_DIMENSION junction table, should I have a standalone Primary Key?

3.) For the CATEGORY_DIMENSION junction table, what should I use for my Foreign Keys?

  • category.id and dimension.id ??

  • category.name and dimension.name ??

4.) For SUBCATEGORY, what should I use for the Foreign Key to DIMENSION?

  • dimension.id ??

  • dimension.name ??

5.) For SUBCATEGORY, what should I use for the Foreign Key to CATEGORY_DIMENSION?

  • category.id and dimension.id ??

  • category.name and dimension.name ??

6.) For the SUBCATEGORY_ARTICLE junction table, should I have a standalone Primary Key?

7.) For the SUBCATEGORY_ARTICLE junction table, what should I use for my Foreign Keys?

  • category.id and dimension.id and sub-category.id and article.id ??

  • category.name and dimension.name and sub-category.name and article.slug ??

Over time, I always seem to change back and forth on which approach to take…

In closing…

  • Should Primary Keys always be an auto-increment Integer?

  • Can Primary Keys be a Text field when a Table is really just a Look-up Table? (e.g. Category, Dimension, Sub-Category)

  • And is it bad for Performance and Referential Integrity if you use Text fields for the Primary and Foreign Keys?

It appears there are several ways the above scenario could be handled, but I’m not sure what the BEST way is… :-/

Help needed!!!

Sincerely,

Debbie

2

i’m not going to analyze your data model, but i will answer your questions

- Should Primary Keys always be an auto-increment Integer?

no, there’s a good argument for using natural instead of surrogate keys (although there is an unusual amount of dissention on this topic)

- Can Primary Keys be a Text field when a Table is really just a Look-up Table?

by text i assume you mean VARCHAR (and not TEXT), and the answer is absolutely, yes

And is it bad for Performance and Referential Integrity if you use Text fields for the Primary and Foreign Keys?

bad for performance? i don’t think so, except in rare situations

bad for referential integrity? absolutely not

3

So what is your preference, and why?

(I have always used ID for joins in the past, because it was beat into me when I learned databases.)

However, when a table has 4 Keys, I must say that this…


finance + accounting + taxes + why-you-should-hire-a-cpa

…would be easier to read than this…


1 + 3 + 2 + 27

- Can Primary Keys be a Text field when a Table is really just a Look-up Table?

by text i assume you mean VARCHAR (and not TEXT), and the answer is absolutely, yes

Okay.

And is it bad for Performance and Referential Integrity if you use Text fields for the Primary and Foreign Keys?

bad for performance? i don’t think so, except in rare situations

I thought one main reason people say to always use ID’s is that they are Integers and therefore quicker for the database to find? (Think Indexes)

bad for referential integrity? absolutely not

I guess if you have “Cascade On Update” set, then there isn’t anything to worry about if I changed a Category from “Small Business” to “Small Biz”, right?

On a Junction Table, should there always be a Primary Key in addition to the Foreign Keys? (Because technically all of the Foreign Keys in total make a PK by themselves?!)

Thanks,

Debbie

4

So what is your preference, and why?

depends on the situation, i use both

I thought one main reason people say to always use ID’s is that they are Integers and therefore quicker for the database to find? (Think Indexes)

i don’t think the minuscule performance differences compensate for the obfuscation that surrogate keys introduce

I guess if you have “Cascade On Update” set, then there isn’t anything to worry about if I changed a Category from “Small Business” to “Small Biz”, right?

yes

On a Junction Table, should there always be a Primary Key in addition to the Foreign Keys? (Because technically all of the Foreign Keys in total make a PK by themselves?!)

yes

5

Okay.

I thought one main reason people say to always use ID’s is that they are Integers and therefore quicker for the database to find? (Think Indexes)

i don’t think the minuscule performance differences compensate for the obfuscation that surrogate keys introduce

That is the same conclusion that I am coming to…

I guess if you have “Cascade On Update” set, then there isn’t anything to worry about if I changed a Category from “Small Business” to “Small Biz”, right?

yes

Will it create problems having Primary Keys or Foreign Keys that have spaces in them (e.g. “Small Business”) or hyphens (e.g. “postage-meters-can-save-you-money”) ?

On a Junction Table, should there always be a Primary Key in addition to the Foreign Keys? (Because technically all of the Foreign Keys in total make a PK by themselves?!)

yes

Okay.

Thanks,

Debbie