Impact of Late-Game Changes to Tables?

Databases
1

So I am wrapping up v2.0 of my website, and being the perfectionist that I am, I would like to tweak MySQL, but am unsure of the consequences of this?! :-/

(BTW, the purpose of these questions is to identify any obvious issues that I might create. Since you guys can’t see my entire code base, I realize my questions below are somewhat vague…)

Proposed Change 1:
I have some Junction Tables which have Composite PK’s, and I would like to add an “id” field that is an AutoIncrement and a UK.

This shouldn’t affect any table relationship.

And it shouldn’t impact any SELECT statements.

Could it impact any INSERT statements that I have in my PHP code? (Or will MySQL take care of the AutoIncrement itself?)

Proposed Change 2:
I have the following table…

article_thread


id (PK)
article_id (U1)(FK)
member_id (U2)(FK)
subscribed_on (U3)

I would like to flip the PK and UK’s.

Will MySQL or any SELECT, UPDATE or INSERT statements, or my PHP care?

As far as I can see, this should only impact my Foreign Key Constraints…

Sincerely,

Debbie

2

Provided that you either used named inserts (INSERT INTO blah SET a=1, b=2) or VALUES INSERTS naming all values (INSERT INTO blah (a, b) VALUES (1,2)) then you shouldn’t have any problem. If you’ve used INSERT INTO blah VALUES(1), omitting a value for b MySQL will complain that you didn’t supply all necessary fields.

Bigger question is, why would you want to do this? Why add a field that adds no information, doesn’t help in your code, and is basically just wasting space?

What is the subscribed_on field? Where will it map in terms of FK?
At first sight I’d say you can change it without any problems, but it depends on your answer on the question above.

3

If I am understanding you, then a query like this should not be negatively impacted, right?


	// Build query.
	$q3 = "INSERT INTO comment(article_id, member_id, comment_no, body, created_on)
			VALUES(?, ?, ?, ?, NOW())";

Some of my tables have 3 and 4 Composite Keys, and it is a real PITA to have to reference a particular record when this is all you have…


article_id	question_id	member_id
1		1		19
1		1		20
1		1		51

In the case of a Junction Table like that, this would be much more practical…


id	article_id	question_id	member_id
25	1		1		19
26	1		1		20
27	1		1		51

The second example allows me to…

1.) Visually spot a record with ease
2.) Helps to clearly define the exact order in which records were entered
3.) If I ever needed to use this table as a parent table, then it would be much easier to do a join using the “id” field versus the other three fields
4.) It gives me another way to sort records to find what I need

So there are 4 reasons right there off the top of my head…

And having one extra “integer” field in a table adds virtually no extra space.

It is a Date-Time field of when the person subscribed.

Sincerely,

Debbie

4

Correct

I’m thinking a question belongs to an article? If so, you don’t need to store both in a junction table. The way you have it set up now is that multiple different members can ask the same question to multiple different articles. If that’s the case then go for it. Otherwise, I’d change the structure.

Why is it in this table? Why not join on your member table instead, I suppose that’s where you copying the value from?

As far as swapping FK and UK, it shouldn’t have any impact, but making subscribed_on an FK doesn’t make sense, since it’s not the PK of any other table. Suppose you make it an ON DELETE CASCADE foreign key, and you have two members who signed up at exactly the same time, and you delete one of them, you delete the article_thread entries from the other one as well.

5

[quote=“rpkamp,post:4,topic:31461”]

I’m thinking a question belongs to an article? If so, you don’t need to store both in a junction table. The way you have it set up now is that multiple different members can ask the same question to multiple different articles. If that’s the case then go for it. Otherwise, I’d change the structure.[/quote]

No. I have an ARTICLE table and a SURVEY_QUESTION table which are joined by a SURVEY_MAP table which defines which Questions are asked for which Articles.

Then I have a SURVEY_RESULTS table which combines the entities ARTICLE, SURVEY_QUESTION, and MEMBER. So things are modeled correctly.

And as mentioned above, it is always easier to look for Record ID = 5823 than to have to scroll down a table looking for a combination of 3 to 4 keys as shown above.

[quote=“rpkamp,post:4,topic:31461”]

Why is it in this table? Why not join on your member table instead, I suppose that’s where you copying the value from?[/quote]

Because an ARTICLE_THREAD is the combination of an ARTICLE, a MEMBER, and a DATETIME.

article_thread


article_id (PK1)(FK)
member_id (PK2)(FK)
subscribed_on (PK3)

(BTW, if it isn’t clear, I have “subscribed_on” as part of the PK because you can “subscribe” and “unsubscribe” as much as you like.

See above.

Sincerely,

Debbie

6

Okay, yes, then your setup is correct.

Okay, then yes you can make the change, but make sure to make one FK only for (member_id, subscribed_on), not two separate FKs, i.e.:


id (PK)
article_id (FK1) (U1)
member_id (FK2) (U1)
subscribed_on (FK2)
7

You are mixing up different concepts…

Concept 1:
In the ARTICLE_THREAD table, I need 3 fields to make a Composite PK: article_id, member_id, subscribe_on

Concept 2:
To ensure Referential Integrity, I have this…


ARTICLE.id -||-----|<- ARTICLE_THREAD.article_id

MEMBER.id -||-----|<- ARTICLE_THREAD.member_id

I am linking two Parent Tables to the Junction Table, so there are only TWO Foreign Keys… (“subscribed_on” provides no linking.) :wink:

Sincerely,

Debbie