say i have certain IDs identifying customers....
the IDs are:
F1231
F1232
F1233
F1234

in this case 'F123' is the actual ID, while the following number(s) are numbers that increment everytime a customer is added with the same ID.
So my problem is:
Say I want to add a new customer with and ID of just 'F', and i need to know if ID 'F' is new, or if not, then i need to find the latest 'F' and give the new customer the next number for ID 'F'.
now my code goes along searching for anything that starts with the letter 'F' and finds the IDs i have stated at the start.
now, how can i get the code to know that 'F' is new and should be assigned the number 1 to become 'F1' and not have it think that there are 1234 'F's already and get assigned the ID 'F1235'??

Now, I have thought that perhaps if i have a separate field that stores EITHER the actual un-numbered ID, or the trailing number itself, then my problem would be solved, but that would require me to change some-lots of 'saving' code which i would prefer to avoid (lazy me ), so can anyone come up with a way of discerning whether 'F' is a new code or not?

lol, after saying this, id probably save lots of time and hassle by just following the alternative solutions i stated in the previous paragraph, and will probably do so just to get this code completed. even so, it would be nice to know if what i am asking is possible

oh yeah, and u cant just strip the numbers off the end of the ID to match em... since if i actually try to add another F123, i wont get F1235, but will instead receive F1....

why the fuck is the ID not just a number? IF this is a database key, you screwed yourself royally dude.

You need a Sequence if this is oracle, and that should be just a number.

trim the ID column... if it is only 'F' then the length will be one. I still think that is stupid though.

And in a 3rd Normal Form database, you should never have multiple different customer id's that are the same person..... you have ONE customer that buys MANY things.

I would only use NUMBER as the datatype. Then I would write a trigger that auto incremented that column every time I called an INSERT statement.

someone is making like harder than it needs to be.

manny, the reason why i numbered the ids is because:

When u are handling customers, you dont want to have some arbitrary number identifying a customer - it doesnt help when you are trying to find data on them. Say if Jeffrey Parker was a customer, i would give him an ID of JEFPAR (first three letters of given and family name) followed by a number, so say he is the first JEFPAR so he would get JEFPAR1. Now lets say a Jefferson Parkinson comes along, now i would have to give him JEFPAR as well, that wouldnt work if i didnt number the IDs, so Jefferson gets ID JEFPAR2.

by knowing that i gave customers IDs (i dont go into the database and find out what to give them - i write an algorithm to give me the ID + number), it makes it so much easier when, say, Jeffrey comes back to the shop, i can just look up JEFPAR (but of course i have search functions that allow you to search by given name, family name, phone number, and ID).

As you said:


believe me, I had auto incrementing numbers as the primary key at first, but it is just absolutely pointless since i dont use it to identify customers with ease.



u misunderstand me, i dont have multiple different customer ids that are the same person, one person = one id, and OF COURSE i have ONE customer that buys MANY things.... im quite insulted that you would think that i would think otherwise :P why i oughta slap u manny



its not that easy, yes u know that the ID that you WANT to add is a length of 1, but how do you know if there are any existing 'F's in the database already? of course you could do a check to see if there are any IDs in the database that are only 'F' minus the trailing numbers, BUT what if the ID actually consists of numbers originally?? as i said previously, F123 could be the original ID followed by a number to become F1231, F1232, F1233, F1234, F1235 and so on... so if you just take the numbers off, you would get many IDs that conflict with each other... F F F F F and F etc....

but yes anyway, ive already fixed my own dilemma with my own stated method as before. lots of additions to the code because of one extra column in the table, but dont have the time to sit n think of a better way.

you can select /+ first rows +/ for what you are looking for using the Like operarator.

it is trivial to look up the F's, but still, make the primary key a composite key then, based on last_name, and incremented cust_id number, and any other measure of uniqness... like date they became a customer. Then you don't need an algorithm that is bound to break as the database changes.

That is horrible database design man. I don't care what anyone says. RULE NUMBER ONE!!!

do NOT place your constraint logic in the FRONT END!!! Make the Database design correct and it does 90% of the work. You are BINDING your application with the database and that is a VERY VERY terrible thing to do....

it is horrible because every time you change the front end, the back end will need modifiing and if you change the table structures, you screwed your whole application front end (or a large part of it)

you ALREADY admitted that fact. you stated that by adding ONE column, you had to add tons of code... get ready for a real nightmare my newbie applications developer.

if anyone should get slapped it is you for doing such a horrible thing.

ALWAYS separate the database and the application. They should NOT be tightly coupled..... and YOU know it.

You can use an auto-incrementing ID field and then create an Index (or two) for the customer name field(s), or any other field you choose to do searches on, to speed lookups. The purpose of an identity field is to assign a unique identifier to each row. You can use other mechanisms, such as indexes (or, if you prefer, indices) and views to make searching for information faster. Manny is correct in his assertions about database structure, he's just not terribly tactful at times. :wink:

sorry bout that... I have just had to deal with TONS of shit others have left for me to clean up.

most times, you have to live with the toxic mess made by others because the company already spent too much developing shit that does not work. Only twice in my career have I been able to develop and implememnt both back-end and front-end systems.

both are alive and well today, easily extensible and maintainable with little to no effort or cost. Both are enterprise class, ultra high performance and truely scaleable. They did not get there by accident.

no such thing as auto incrementing in ORACLE dude.... do it with a trigger on a before insert.. and it uses what is called a SEQUENCE. I am used to that stuff... not the push-over SQL Server (easily turned into a ZOMBIE with worms and such) or the laughable MySQL junk.

However you go about it, dude. :wink: Same principle, no matter how it gets implemented. BTW, Oracle has at least as many patches out as SQL Server (Oracle calls them point releases), it's just that no one bothers to target such a small installed base.

ok, like, dont laugh but i dont have Oracle / SQL etc to put my database on... all i have is Microsoft Access as my database and i am forced to program in VB6 that is included with Access.

I am just doing this as an experience thing for a really small company that i work for (computer shop). They dont have thousands upon thousands to fork out for the right software. Besides, i got sick n tired of doing invoices/quotes/credit notes on Microsoft Excel, and flipping through pages and pages of invoices to find a customer's contact details. it sucked, so i offered to do something simple for them. only 2 people at most need access to the database from the application at once for invoicing/purchase orders.

All i have is the database backend sitting on the "server" (i dont even know why they have one, it just sits there doing nothing 85% of the time, the other 15% from the database getting accessed), and the frontend on the payment/enquiry desk.
its just a simple 2-tier client-server architecture....

forgive me manny if i am commiting such a sin, but hey, gotta start somewhere and you're really kicking me down the shithole



well, it wasnt THAT much code really, just adding a few words to the datamanager classes so that i can get/save the new column data from my frontend forms.

You can do as I suggested within Access. Access has an Autonumber data type for a column, which you can combine with the name of the customer as a composite primary key. I may be able to help you out a bit, but I'll need to know what version of Access you're using.

lol... oracle has the lion's share of the market as far as installed databases.... SQL server is still wetting itself... and is NOT enterprize class..

sorry for being a sour puss... i am just sulking....

Actually that title belongs to DB2 now... :wink:

Sorry that you're sulking, Manny, and I hope you find a reason to be cheerful again VERY soon!

Mephist, PM me and I'll see what I can do to help you out. I've been working with Access since Access 2.0, so I may have a couple of ideas you can use.

I used to use Access to prototype.. but, as Oracle 9i (everything) is free to developers.... I just use that and 9i Designer and 9i Application Server on my uber-fast nForce.

You can also get MSDE (Desktop SQL Server) free from mickeysoft and Sybase Adaptive Server for developers too for free.

If this is to be used in MS with Office apps, stick to MSDE. There are limitations with MSDE, like the fact that the database can grow to only 2 GB (which is HUGE). there are also limitations to the number of connections but you can use MTS to limit connections to a small pool with everyone connecting using the pool of connections.

If the customers want, they can, at any time, convert to full blown SQL Server without changing any code. It is a limited version of SQL Server.