Assign my own numbersystem in MySQL DB.

[LeonLatex]

How do I get the my MySQL database to enter my number system to assign an ID number space that goes from 01, 02, 03 etc. from 10 and up to 99 there should still be two numbers as in 10, 11, 12 etc. From 100 three numbers as in 100, 101, 102 etc. When a member is deleted from the list, the number must be able to be used again.

I tried a few different solutions here, but managed to delete everything in the end. Only the databases are left. These numbers should not only be reusable, but also reusable and automatically dialed when the member registers. As I said, the number will be automatically chosed if it is available. They must also be transferred and selected into other DBs as the ID already assigned as.

Exactly this part of the database I am struggling with now. If you do not understand what I mean, please ask me. I will answer as best I can.

Thanks for the help you give me.

[Zane]

Can you emphasize a bit more about your number system?

As far as keeping the number, you would need to keep those in another table that has a status column called, say, "available".  The value would be 0 or 1.
Then, you can do a query to get or set the availability of the number. 

Give said table a "member" id column and you can link the two the tables (members table and the numbers)

It may help if you described more what this "number" is that must be recyclable.  Lastly, the rule of thumb for a normalized database is to always have a primary key that is just integer auto-incremented, nothing more.  If you want to do further indexing, then add a unique index column for foreign keys to your "number" table.

[Barand]

I would created a pre-populated table "member_no" which contains a row for each of those values.

create table member_no (
  memno varchar(3) not null primary key,
  member_id int
);

The "member" table would have a conventional auto_incremented numeric id (not re-used).

TABLE member_no                       TABLE member
+----------+------------+             +-----------+------------------+------
| memno    | member_id  |             | member_id | name             | etc.
+----------+------------+             +-----------+------------------+-----
|     01   |    1       |             |     1     |  Curly           |
|     02   |    2       |             |     2     |  Larry           |
|     03   |    9       |             |     8     |  Mo              |
|     04   |   17       |             |     9     |  Fred            |
|     05   |    8       |             |    15     |  Wilma           |
|     06   |   15       |             |    16     |  Barny           |
|     07   |   16       |             |    17     |  Betty           |
|     08   |   null     |
|     ...  |   null     |
|    999   |   null     |

When a new member joins (say, id=18) they assigned to the first memno with a null member_id (08).

When a member leaves, their member_id in the member_no table  is set back to null.

You could use triggers on insert and delete to automate the process.

[LeonLatex]

Zane, thanks for your reply. I can try to answer. It so happens that I have a membership database for a boating association with members. These members must be / are linked to a number, which is the berth on the pier they belong to. This number is determined by me and I want there to be ID in the table (s). This is basically what I want to do in the first place, but which I do not understand how to do.

Secondly, I want these numbers / berths to be able to be used again. When a member is deleted because he acquires a boat that is larger or smaller, a berth / number on the pier is released. This number must be able to be used again by others when a member is deleted and transferred to another berth, and another jetty number that is available. Hope you understood a little more of what I meant now.

I think Barand is talking about something in his proposal. I'll check out and see what I come up with. I will definitely come back with more questions, about this issue or a new question, and I will certainly be able to help solve other people's problem as time goes on. I'm new to both the site here and MySQL and PHP.

I will also eventually be able to contribute with donations. What is a normal sum and donate to those who help me. Will not seem like someone who is stingy and gives too little, but I do not have much money to give away. So how much is accepted as enough money when i donate?

Edited April 12, 2021 by LeonLatex

[Barand]

Now you have told us the actual context it makes a lot more sense than having reusable member IDs. You also introduced a couple of other attributes that would be stored in the "berth" table (renamed from member_no) viz. Size and Pier.

Size required would need to be known at time of allocation to a member.

Reallocation of a different berth would need to be a custom transaction, allocation on insert could be a trigger function, de-allocation could be a foreign key cascade option.

4 hours ago, LeonLatex said:

When a member is deleted because he acquires a boat that is larger or smaller,

Your assertion that a member is deleted when their boat changes is FUBAR. Change, or add, a record in the member boat table. The boat size in this table would determine the size required for the new berth.

 

or

Edited April 12, 2021 by Barand
image

[LeonLatex]

Barand, thank you soooooo much. I am on the right way now i can see. Just what i needed. Hope i can come back to you for further questions.
What kind of MySQL GUI do you use for draving the database?

[Barand]

I use MySQL Workbench (it's free and I hate phpMyAdmin)

[LeonLatex]

Thanks. neither do I like phpMyAdmin. I will try out MySQL WorkBench.

[gizmola]

I do want to just throw in my 2 cents and say that the schema Barand provided is the simplest solution, but not the one I would use for a variety of reasons.  In general, a better solution is to design things so that when there are changes, as in for example, a boat is moved from one berth to another, you don't lose history.  Another example, might be a boat being sold to a different owner.  Certainly you could just edit the boat record and change the owner, but you'd in the process lose the data that showed who the original owner was.  

In general the solution to this is to add many-to-many relationships which require an additional table and "fromDate" and "toDate" columns.  It does add some complexity to your queries as well because you need to be concerned with datetime ranges, but once you understand how to query with that additional criteria, it's fairly simple.

Adding to Barand's example, I would personally have a table named  boatBerth or berthBoat.  This table will resolve the many -to-many relationship between boat and berth.  This table would need it's own primary auto_increment key, and a foreign key to both boat and berth.

The final ingredient would be fromDate and toDate columns, establishing when this row should be considered "active" or "current".

berthboat
=========
berthboat_id (primary key auto_increment)
boat_id (fk to boat)
berth_id (fk to berth)
from_date (datetime)
to_date (datetime)

In order for this to work, the secret is that to_date for an active row should be set to the max allowable datetime which is for simplicity sake '9999-12-31'.  A datetime has a time component, but if you omit the time portion when doing an insert or update, it will be upscaled to include a time, which isn't important.

So lets say that you have boat #7, that is going to be docked in berth #23.  Here is an example of the data in berthboat:

berthboat_id:  1
boat_id: 7
berth_id: 23
from_date: '2021-04-14 12:32:17'
to_date: '9999-12-31 00:00:01'

Next week, boat# 7 gets moved to berth: 29.  What needs to change?  

1st, update the to_date for berthboat_id to be the day/time the boat was removed from berth# 23.

berthboat_id:  1
boat_id: 7
berth_id: 23
from_date: '2021-04-14 12:32:17'
to_date: '2021-04-18 09:01:01'

This indicates that at 9am the boat was taken from berth: 24.  Look at all the information you retained:  You know that boat#7 was in berth 23 from April: 14 to April 18.  

Once it's docked in berth 29 a new row is added:

berthboat_id:  2
boat_id: 7
berth_id: 29
from_date: '2021-04-18 15:32:17'
to_date: '9999-12-31 00:00:01'

This shows that boat#7 was moved and perhaps after some maintenance was docked in berth# 29 at 3:32pm.  Times might be important for billing purposes or general history.  You now have history either for the boat  (was in berth 23, then moved to berth 29) or for the berth (boat 7 was in berth 23.  It is currently empty.)  

MySQL like most relational database engines has some useful date/datetime functions that make determining what the active rows are.  So for example, omitting the joins to boat and berth that you would probably want to do, depending on the data you're pulling, let's just say you only wanted the current active berthboat rows.  MySQL has CURDATE() and NOW() you can use, which are equivalent to the current date or current date/time.  So let's say in 3 weeks time, your system should only show a list of the current active berthings.  The underlying query could be as simple as:

 

SELECT * from berthboat WHERE NOW() BETWEEN from_date AND to_date

 

In this case, your result will contain the row with berthboat_id = 2, but not the original berthboat_id = 1 row.  This allows you to retain the history of all berth assignments, and could even be used for scheduled movements in the future, because you could have a from_date of next month, indicating that a boat is scheduled/will be moved to a particular berth.

That row can co-exist with the row indicating that a boat will be removed from a berth in a couple of weeks.  Most of the time all you are doing is updating the from_date of a berthboat row to indicate it is no longer associated with a berth and/or adding a new berthboat row to indicate a boat now resides in a berth.   

When you don't know how long a boat will be in a particular berth, you use the "perpetuity" date I illustrated previously.

A similar scheme could be used for the relationship between a boat and a member.  Again this would allow you to keep track of which owner owns a particular boat at a point in time, while not losing the information that previously it belonged to another member, and was perhaps sold to a new member.  

Again, this is probably what you want to do, but then again, I don't really know the full extent of your use cases, so it also might be overkill.  The important distinction between this design and the original one from Barand, is that Barand's design will allow you to reflect the state of things at present, but can not retain any historic data, nor support advance pairing of boats and berths.  

[Barand]

@gizmola I note your comments regarding dates and, had this been for a commercial marina with many comings and goings, stays of varying duration and future bookings, I too would have put them in the schema. However I was under the impression that this situation was more like allocating semi-permanent car-parking spaces to employees.

[gizmola]

1 hour ago, Barand said:

@gizmola I note your comments regarding dates and, had this been for a commercial marina with many comings and goings, stays of varying duration and future bookings, I too would have put them in the schema. However I was under the impression that this situation was more like allocating semi-permanent car-parking spaces to employees.

Totally agree.  My comments were basically just musings to provide an alternative albeit more complex solution, for the purposes of discussing what additional relations might provide functionally.

[LeonLatex]

Thanks to both of you. 

You have really helped me along the way with the database. Barand got me started with his ideas, and then gizmola came up with some add-ons that were in my head as add-on features to enter into the database later. It was the addition of gizmola that was missing to get this to vote. With the table berthBoat I will easily be able to add these extra features afterwards.

gizmola and Barand, you are talking about something when you talk about how the system in the boating association is. It is thus the case that there is an association with permanent members where the membership is year-based, but continuously until the member terminates the membership. That is, you receive an invoice once a year. This applies to both support members who are without a boat or a member with a boat and berth. Possibly in anticipation of berth becoming vacant (then the member is listed in a waiting list). I will probably need some help with this waiting list later as well, but it will come when the rest of the system is ready for it.

As it has been suggested from their side until now is completely in my street for how the system should be, what data should be stored and retrieved etc. Just like a history section about each member. The plan is for each note to be saved each year before it is deleted. It will not be deleted permanently, but transferred to a separate table for a register for posterity, but which will not have a negative effect, only storage of what a person has done, etc. if you understand what I'm talking about. A lot of this is in PHP scripting as well, these are some of the plans for my boat association's system. I will also include some functions with regard to invoicing directly from the member list. This is far into the future.
I'll now give you both a small donation. It's not much, but that's what I can do now for this time (gizmola) I did not get to donate anything to because he does not have a paypal account). Hope there is so much that I can get more help when I need it Barand. This system is becoming more advanced than first thought. Ideas are created as time goes on. Thanks for the help so far.

P.S. What meens FUBAR?

Edited April 22, 2021 by LeonLatex

[Barand]

9 hours ago, LeonLatex said:

What meens FUBAR?

F***ed Up Beyond All Recognition. I think it's an army acronym, like SNAFU(Situation Normal, All F***ed Up)

[LeonLatex]

9 hours ago, Barand said:

F***ed Up Beyond All Recognition. I think it's an army acronym, like SNAFU(Situation Normal, All F***ed Up)

Ahaaaa... Then I understand why I did not remember this from either mySQL (SQL) or PHP. Did not find anything about it online nor other than what you say is the meaning of FUBAR. Still, I had to ask in case there was something important I had forgotten or overlooked.

[gizmola]

On 4/21/2021 at 8:25 PM, LeonLatex said:

I'll now give you both a small donation. It's not much, but that's what I can do now for this time (gizmola) I did not get to donate anything to because he does not have a paypal account). Hope there is so much that I can get more help when I need it Barand. This system is becoming more advanced than first thought. Ideas are created as time goes on. Thanks for the help so far.

That's very nice of you, but certainly nothing we see often.  With that said, there's literally a link in my sig to my paypal account, so I'm curious if you didn't see that, or it doesn't work?  

[LeonLatex]

5 hours ago, gizmola said:

That's very nice of you, but certainly nothing we see often.  With that said, there's literally a link in my sig to my paypal account, so I'm curious if you didn't see that, or it doesn't work?  

What i wanted to say, i couldnt find a link like the one Barand have.  will look again and as fast i find a link like the one Barand has i will donate you the same amount. EDT: I found a link. I have now donated you both 100 NOK

Edited April 26, 2021 by LeonLatex
Found a donation link.