Database model

[r937]

@spiderling

He would then have a different member_id.

ouch!!

rel_member_id  num_units_own  num_units_manage
   1 (curly)             3              14
   2 (larry)             2               9
   4 (larry)             3              37
   3 (moe)               1              42

@spiderling

Can I still use the 3 fields as the PK in the member_num_units_rel table?

i wouldn’t, because what you want is one row per member, but with a 3-column PK you could have more than one

okay, you said he’d have two different member ids, but the point is, you should let the database ensure uniqueness, rather than relying on application code

@spiderling

Do I add the rel_member_id FOREIGN KEY back?

yes, because you don’t want any rows in here for member_id values that do not exist in the members table

right?

@spiderling

If not, why is the KEY for the member_company_rel table kept and not the member_num_units_rel table?

KEY means INDEX – it is a physical structure that allows for performance optimization

unfortunately mysql uses both KEY and INDEX to mean the same thing, when it should allow only INDEX

FOREIGN KEY and PRIMARY KEY are not at all the same as KEY – with the exception that a PRIMARY KEY automatically gets an INDEX, and so does a FOREIGN KEY after version something-or-other (you used to have to declare the INDEX for a FOREIGN KEY yourself)

now i’m sure after all these posts we are both thoroughly confused, so if you need further help, i would suggest you do your SHOW CREATE TABLEs again, as i know i am already half lost

[spiderling]

@r937

i wouldn’t, because what you want is one row per member, but with a 3-column PK you could have more than one

okay, you said he’d have two different member ids, but the point is, you should let the database ensure uniqueness, rather than relying on application code

I confused myself thinking that the combination of the 3 fields would create a unique value together and prevent duplicates. Thinking about it again proves that wrong, because an insert can still be done with the same member_id so long as one of the other values is different. I decided to make the PK the rel_member_id as well as making it the FK.

CREATE TABLE `mrm_member_num_units_rel`
(
  `rel_member_id` MEDIUMINT(5) UNSIGNED NOT NULL,
  `num_units_own` SMALLINT(5) UNSIGNED NOT NULL,
  `num_units_manage` SMALLINT(5) UNSIGNED NOT NULL,
  PRIMARY KEY (`rel_member_id`),
  KEY (`rel_member_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

[r937]

@spiderling

I decided to make the PK the rel_member_id as well as making it the FK.

well, what you posted doesn’t actually have a FOREIGN KEY

plus, your KEY – which is the equivalent of INDEX – is actually redundant

  PRIMARY KEY (`rel_member_id`),
  KEY (`rel_member_id`)

a PRIMARY KEY gets an index by default

when you declare this KEY, which is an index, it is actually redundant

KEY is ~not~ the same as FOREIGN KEY

[spiderling]

@r937

well, what you posted doesn’t actually have a FOREIGN KEY

plus, your KEY – which is the equivalent of INDEX – is actually redundant

  PRIMARY KEY (`rel_member_id`),
  KEY (`rel_member_id`)

a PRIMARY KEY gets an index by default

when you declare this KEY, which is an index, it is actually redundant

KEY is ~not~ the same as FOREIGN KEY

The desktop software (Navicat for MySQL) I am using shows it as KEY. When I create the table, under the Foreign Keys tab I set the rel_member_id as a foreign key. However, when I use SHOW CREATE TABLE, it shows as KEY.

Aside from it showing as KEY, can I set rel_member_id as a foreign key, or do I even need to?

[r937]

@spiderling

However, when I use SHOW CREATE TABLE, it shows as KEY.

there is a word for this type of result, and that word is “wrong”

@spiderling

can I set rel_member_id as a foreign key, or do I even need to?

yes you can and yes you should

[spiderling]

@r937

there is a word for this type of result, and that word is “wrong”

I’ll contact them regarding this.

Thanks again for all your help!

[spiderling]

I can’t use foreign keys since the engine I’m using is MyISAM. I would have to use InnoDB, which from what I was able to gather wouldn’t suit my situation.