Is there a reason group_id is not the primary key? about pft HOT 5 CLOSED

tl;dr - Yes, but might not be the best now that I look at it again.

So I am under the impression that when you make a relationship between tables you need to create a foreign key in the referencing table that points to a primary key in the referenced table. I initially only had one column in the groups table as the name of the group. It turns out it was helpful when selecting groups to have a group_id so I added that in afterwards.

Additionally, I felt it would be nicer to have the group name referenced directly in the envelopes table so we don't need any additional steps going from group id to group name for displays, etc...

Now that I am looking at it, I did the opposite in the transactions table, but have been treating it like the name is the reference. I will have to look at things again and see if it makes sense to just use id's as primary keys and references. We might just have to write some functionality to go from id to name and make sure the classes have an id variable that is defined when creating an object from a table entry. hmmm....

Good catch. Any suggestions?

from pft.

What I understand is that generally for performance and storage, using integers as primary keys is better. Basically that way you don't have to store the string in every table, and joining is faster.

Apparently it's quite a bit more complicated than that: Stack overflow links here and here. But basically it seems like as a general rule, using an int is the best.

I think the function you are talking about is called joining unless I misunderstood you. It's one of the key functions of relational databases.

I'm not 100% sure of the syntax, but something like

SELECT env_name, group_name
FROM envelopes, groups 
WHERE envelopes.group_id = group.group_id

Would give us access to a table of all of the envelope names and the corresponding group names by joining on group_id. Then we can manipulate that for display or for whatever else we need however we want.

from pft.

Although now that I think more about it, what I’m not sure about is how we will get input. Like if I say I want to deposit a transaction into x account. It will have to query the account table to look up the account_id to store it in the transaction table.

That might be more tricky but I bet there is probably a pretty simple way with SQL

from pft.

i think using id's will work fine and will just need some joining as you described. when entering a transaction (deposit to account x) I currently list the accounts and you enter the ID to select the account, so we can just store that in the transaction table instead of the name of the account. It might actually be slightly easier.

from pft.

Ok, in the input validation you will confirm that the number entered is a valid entry right? If so, then this is fine. I could see this causing problems later if accounts or envelopes are deleted and created, then the primary keys will change, and it could be confusing to see a list with skipped numbers. But for now, its fine

from pft.