Composite primary key

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
sjohn
Veteran Member
Posts: 86
Joined: 2018-05-23 09:32

Composite primary key

Post by sjohn » 2018-06-29 10:06

Scenario :

A table with username, year, month, transaction text.
In the table, there could be more records for the same user.
And more records for the same year, and more records for the same month.
There could also be more records for the same user for the same month.
BUT - there can only be one record for the same user, year and month.
In other words - there can only be one registration for the same user pro month.

Normally this would be solved by making the 3 fields :
User
year
Month
As a composite primary key. All three fields I set as primary key in the table in AppGini.

This is not allowed.

How then, to do this?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Composite primary key

Post by pbottcher » 2018-06-29 10:35

Hi,

you need to do this directly in your Database.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

sjohn
Veteran Member
Posts: 86
Joined: 2018-05-23 09:32

Re: Composite primary key

Post by sjohn » 2018-06-29 10:52

Thank you pböttcher

And this is not affected by new uploads ?

sjohn
Veteran Member
Posts: 86
Joined: 2018-05-23 09:32

Re: Composite primary key

Post by sjohn » 2018-06-29 10:59

Just tested - Appgini will not allow a table without a primary key.
And if I make a primary key, then it will give no meaning to specify also other fields as keys.
All records will then have a unique key as the key specified from Appgini will always be unique.

Or should I specify just one of the fields as primary key, and then, in the database make the other two also primary keys?
And what would happen when re-uploading - the key specified from Appgini as primary key will not be unique just after upload - this should give an error.

Or have I misunderstood what you mean?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Composite primary key

Post by pbottcher » 2018-06-29 11:44

Hi,

as you sayed, you need the primary key in appGini, but you still can add a unique key to your database.
So in your case I would add a field ID that would identify each record and be the primary key.
Otherwise you can remove in the database the primary key set via appGini and create your unique key as needed.
I think AppGini will not recreate the primary key for what is marked in the GUI once you recreate your app as long as you do not modify the PK settings for this field.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

grimblefritz
AppGini Super Hero
AppGini Super Hero
Posts: 336
Joined: 2015-12-23 16:52

Re: Composite primary key

Post by grimblefritz » 2018-06-29 12:22

I have not an answer, but a question.

Is your application so large or busy as to truly benefit from an optimization such as multi-field keys?

In my experience, unless you're dealing with large record sets (many tens or hundreds of thousands of rows) or have a very busy transactional system - there's little or no benefit to such optimizations. If neither of those applies, then (unless you're in it for the technical merits rather than the usefulness of the application) why expend valuable developer time on a tweak that will still leave the system wasting most of its time waiting for user input?

Just something to consider.

sjohn
Veteran Member
Posts: 86
Joined: 2018-05-23 09:32

Re: Composite primary key

Post by sjohn » 2018-06-29 12:49

Hello Grimblefritz

It is NOT because of an optimization. Not at all.

It is simply because of the case described in the link in the first post :

A way to make sure that you for a user ( or a car or some other thing ) only have one registration per month ( or per type or per whatever ). If you ( would be a mistake ) tried to make another registration for this user on the same month, you should get a message that this is not allowed. ( violation of the primary key )
A possibility to specify more fields as keys, will do that you can have several registrations for each field value, but you can only have ONE registration for the whole combination of the fields specified.

i am maybe not able to specify what I want, but I know that making more fields a composite key, is a quite common thing.
Often used in sql-databases.
In the cases described, it is very useful.

I will now try to make some tests, where I follow Pböttchers advice, and make changes directly in the database, and then see what happens when changing and re-uploading.

sjohn
Veteran Member
Posts: 86
Joined: 2018-05-23 09:32

Re: Composite primary key

Post by sjohn » 2018-06-29 13:03

Sorry - there is no link in the first post. But there is a short description in the first post :-)

grimblefritz
AppGini Super Hero
AppGini Super Hero
Posts: 336
Joined: 2015-12-23 16:52

Re: Composite primary key

Post by grimblefritz » 2018-06-29 13:20

I realize multi-field keys are commonly used. My point is, since it's not a feature AppGini supports, and I'd argue it's not a crucial feature in an AppGini-style application, then:

1. Why would Ahmad expend his time to add support for them into AppGini?

2. Why would you expend your time to find a way to hack them into an app?

When, a simple query placed in the before insert/update hooks, would prevent the situation you're describing?

Not as technically elegant or concise as a multi-field key or code-free as multi-field keys, but it would do the job just fine and with a lot less effort and risk than trying to hack around what AppGini may stomp on.

sjohn
Veteran Member
Posts: 86
Joined: 2018-05-23 09:32

Re: Composite primary key

Post by sjohn » 2018-06-30 08:38

Hello Grimblefritz

I know many things can be made via hooks. In fact all could be made via hooks - or even without hooks or without AppGini - only by using PHP, Js, queries and some other things.
I see Appgini as a magic tool to create database-applications without need to code. Then there will be things that come :

1 - Special things, wanted by one or few users. This should be made by hooks.
2 - Valuable functions, wanted by users, or that users did not know they wanted. This could/should be made by hooks and there should be a place where those code-bites were described and could be "clipped". An example of this, could be the scrolling facility presented by Pböttcher. This is a rather useful function, it is easy to implement, and it can be put in a hook. By time items under 2 could be transformed to an item 3.
3 - Functions that many users could benefit from. This should be functions that hopefully will be implemented in the AppGini.

1+2 is items that could/should be posted on the forum, and hopefully members here, will help with a solution, and in details describe how it could be implemented in a hook.

3.This is also items that should be posted on the forum. These items is to be processed by Ahmad
He is the Magician that rules the magic land of AppGini.
If he decides that there will be too much work in relation to the outcome, or that it is not of enough common interest, then decide that it will not be implemented, but instead, maybe describe an alternative solution to a problem.

Even if a composite key could be useful, I agree that it could fall under category 2. However - I am still not sure how to make this, or if it will be better to make a hook that construct the value for a single key, or let it be a matter of a check, or to make changes in the database.
I will experiment with this.

Before I posted the "composite key" I had searched the forum and found some posts related to the "composite key" scenario, but I found no answer.

If someone has a smart solution, that involves the same functionality that the composite key gives, I would like to see it posted.

grimblefritz
AppGini Super Hero
AppGini Super Hero
Posts: 336
Joined: 2015-12-23 16:52

Re: Composite primary key

Post by grimblefritz » 2018-07-01 16:28

sjohn,

As an author and/or contributor of several open source (and before that, shareware) applications, I can say with confidence that Ahmad has to also balance the complexity of each new added feature against his ability to subsequently support it. I see many things in AppGini that could be done, and rather easily.

However, I also see (by way of experience) that support would become a nightmare. Users in general and collectively have an endless appetite of users for more features. And there is always the few users whose outrageous demands and behaviors create a disproportionate negative presence.

I happen to agree with you, that multi-field keys would be a benefit. Even function-based keys. I don't think, however, that it is a necessity and would have no issue if Ahmad elects to not support them.

AppGini is a great CRUD tool and app generator of that ilk. Another plus is that it can be fairly easily extended via hooks. It is not, however, a full-featured database manager nor a robust full-scope application builder. It's great at what it does, but stray too far from it's intended purpose and the greatness dims.

So, as to your problem, I would write a single function, used by the before insert hook, and perform a select that queries each of your multiple "key" fields. If there is a match (ie, a non-zero result) then the new record is invalid. Return a result accordingly. This will prevent the insertion of duplicate keys.

For updates, you will need to ensure that none of the key fields change. This doesn't even require SQL. Or, if you allow edits in key fields, then you have to ensure that they don't create a duplicate (using the same logic as the insert hook.)

Going another route, you could create a new field, with a required unique value. In the hooks, you would pad and concatenate the various key values and store them in this field. This is essentially what a multi-field key does - only without the additional field. Is it "proper" data normalization? No. But sometimes, function is more important than academics.

Hopefully, this will point you in the right direction.

sjohn
Veteran Member
Posts: 86
Joined: 2018-05-23 09:32

Re: Composite primary key

Post by sjohn » 2018-07-04 08:28

Maybe it is because I am an old dog.

In my former life, I have been using composite keys. Maybe I just need to think alternative.
But I would still want the possibility to have the system "construct" a key. Maybe it don't even need to be a primary key - It could maybe just be an extra field that was set as unique. I am not sure....
I can see that Ahmad will consider.
I am sure that he will find the best solution if there is a best solution.

Post Reply