473,836 Members | 1,429 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

auto generate second primary key

Hi,

I am building a products database, linking sales and production. Each
part has a unique sales Stock Code and Production Number.

The sales stock code is a combination of letters and numbers taken
from the part name (BrandA 300 4 Bladed : B3004B). The production
number is a 4 or 5 digits number with first figure(s) indicating
category (BrandA 300 4 Bladed: BrandA is category 1000. This Product
would be 1003 if it was the third created in this category).

I want to use the Sales Stock Code as primary key (OK so far). Then I
want Access to auto generate the production number following these
steps:
1- input new Sales stock Code
2- select category
3- autogenerate production number (if first product from category
3000, give No 3001, second product 3002...).

Thank for your help. Please, contact me if you need more info

Sebastien
Nov 13 '05 #1
6 3820
You talk about wanting to create these fields, but not really what you want
to do with the fields, which I think is a very important question. So
without more information, it's hard to fully answer, but ..
I want to use the Sales Stock Code as primary key The sales stock code is a combination of letters and numbers taken
from the part name (BrandA 300 4 Bladed : B3004B)


While this may sound like a good idea, I would caution against this. I know
others have said it, and I can only echo it: every time I've using anything
other than some form of auto-generated numbers for a primary key, I've
regretted it later. You may end up doing contortions with naming to make
your PK unique. Someone may decide that "Bladed" is now referred to as
Knife-edged", so do you alter your primary key? What if you want to add a
product, but don't know for sure that its Bladed - now you can't add it and
edit the details later, because you need *complete* infor to get the PK
right - and you should *never* have to change the PK.

You can have more than one autonumber field, so if you want one for your PK
(a wise choice, my friend ...), you could use another to generate the
production number.

Since you're in the design stage, I'll also say:

For every table I use, the PK is the name of the table + "_id", as in
"offender_i d" and "supervision_id ". They are always Long Integers,
autonumbered.
I never put spaces in field names, so I never have to use brackets [feeled
name].
I prefix all field names (except the ever-present 'updated' and
'updated_by') with a short version of the table name + "_", so for the
offender table:

offender_id
off_last_name
off_first_name
off_suffix

This way I never have to be concerned with ambiguous references - every
field name is unique in the database (two exceptions above).

Foreign keys are named by the table prefix and the foreign table prefix; in
table address:

add_off_id

This way, I always know exactly what it is - the PK of the offender table as
a FK in the address table. This makes creating joins so much easier, and
keeps names unique.
My 2 shillings.
Darryl Kerkeslager
"Sebastien" <Se*******@jo-at.co.uk> wrote in message
news:cc******** *************** ***@posting.goo gle.com... Hi,

I am building a products database, linking sales and production. Each
part has a unique sales Stock Code and Production Number.

The sales stock code is a combination of letters and numbers taken
from the part name (BrandA 300 4 Bladed : B3004B). The production
number is a 4 or 5 digits number with first figure(s) indicating
category (BrandA 300 4 Bladed: BrandA is category 1000. This Product
would be 1003 if it was the third created in this category).

I want to use the Sales Stock Code as primary key (OK so far). Then I
want Access to auto generate the production number following these
steps:
1- input new Sales stock Code
2- select category
3- autogenerate production number (if first product from category
3000, give No 3001, second product 3002...).

Thank for your help. Please, contact me if you need more info

Sebastien

Nov 13 '05 #2
use DMAX(SomeField) +1 to get the next value and then build the string yourself.
Nov 13 '05 #3


Thank you Darryl,

I will probably choose an autonumber for my primary key and select my
sales code as a text field with no duplicate (got to redesign my
database :( ).
To give you more info about this prod. number: I need this production
number as it is the code we use to schedule parts in production.
Therefore, it is also unique for each part.
Further, I want to be able able to do some searches to have a full
product description using category, sales code or production number.
Regards,
Sebastien

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4
One of the principles of database design is to keep your data in the
smallest meaningful pieces. Doing so speeds up searches and other queries,
usually decreases the size of your database, makes finding meaningful
information easier, allows for better indexing, and other benefits I can't
think of off-hand.
The most basic example, of course, is a name, that you store separately as
first name, last name, middle name, and suffix. Another is splitting
addresses into house number, street, apartment, and state, and zip code,
which also yields obvious benefits.

If you just store all that information in one field, you lose that ability
to make fast and easy queries.

However, I also have found that more often than not, I want to look for the
whole name, or the whole address - so even though it uses space, I also
store the full name and the current address as single fields, so that I
don't have to rebuild the information so frequently. Since speed is more
important to me than space, this makes sense.

My recommendation would be to do the same with your product number: store
each element separately in fields, but also, at the time you enter those
fields, create the full product number and store that in its own field.

Darryl Kerkeslager

"Sebastien Lagadec" <se*******@jo-at.co.uk> wrote in message
news:41******** **************@ news.newsgroups .ws...


Thank you Darryl,

I will probably choose an autonumber for my primary key and select my
sales code as a text field with no duplicate (got to redesign my
database :( ).
To give you more info about this prod. number: I need this production
number as it is the code we use to schedule parts in production.
Therefore, it is also unique for each part.
Further, I want to be able able to do some searches to have a full
product description using category, sales code or production number.
Regards,
Sebastien

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #5
DFS
Darryl Kerkeslager wrote:
One of the principles of database design is to keep your data in the
smallest meaningful pieces. Doing so speeds up searches and other
queries, usually decreases the size of your database, makes finding
meaningful information easier, allows for better indexing, and other
benefits I can't think of off-hand.
The most basic example, of course, is a name, that you store
separately as first name, last name, middle name, and suffix.
Another is splitting addresses into house number, street, apartment,
and state, and zip code, which also yields obvious benefits.

If you just store all that information in one field, you lose that
ability to make fast and easy queries.

However, I also have found that more often than not, I want to look
for the whole name, or the whole address - so even though it uses
space, I also store the full name and the current address as single
fields, so that I don't have to rebuild the information so
frequently. Since speed is more important to me than space, this
makes sense.
?? You've just effectively negated all the proper reasoning you displayed
in the first paragraph.

Better that you enter the data once in an atomic form, then "recombine" it
with a query, and do searches against the query.


My recommendation would be to do the same with your product number:
store each element separately in fields, but also, at the time you
enter those fields, create the full product number and store that in
its own field.

Darryl Kerkeslager

"Sebastien Lagadec" <se*******@jo-at.co.uk> wrote in message
news:41******** **************@ news.newsgroups .ws...


Thank you Darryl,

I will probably choose an autonumber for my primary key and select my
sales code as a text field with no duplicate (got to redesign my
database :( ).
To give you more info about this prod. number: I need this production
number as it is the code we use to schedule parts in production.
Therefore, it is also unique for each part.
Further, I want to be able able to do some searches to have a full
product description using category, sales code or production number.
Regards,
Sebastien

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #6
As I mentioned later, size is less important than speed. While having 2-5
atomic fileds, AND one combined field may take up twice the space, with the
size of hard drives, size on a PC or server is inconsequential . Using both
atomic and combined fields, you get the benefits of fast searches of both
the atomic elements - like a first name or nickname in an alias field AND
you get faster queries on the whole name, since, for instance, you don't
have to re-combine 30,000 names every time you want to print a report with
the complete name.

This is even more important where the combination is not so simple as
first_name + last_name + middle_name + suffix = name. For instance, I have
addresses stored as (possible jail/apartment/other facilty id) +
house_number + street_id + apartement + (city-state-zip id). Each id field
must be queried to obtain the actual text, so building one address is a
complex query; imagine the processing to print out a list of 100 offenders
with addresses - or 1200.

Since I only store the current address in a combined field (not every
address in address history), the actual space used is not so much, and I
still get the speed benefit.

Darryl Kerkeslager

"DFS" <no****@nospam. com> wrote:
?? You've just effectively negated all the proper reasoning you displayed
in the first paragraph. Darryl Kerkeslager wrote:
One of the principles of database design is to keep your data in the
smallest meaningful pieces. Doing so speeds up searches and other
queries, usually decreases the size of your database, makes finding
meaningful information easier, allows for better indexing, and other
benefits I can't think of off-hand. [snip]
If you just store all that information in one field, you lose that
ability to make fast and easy queries.

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
2796
by: csomberg | last post by:
SQL 2000 I thought I would throw this out there for some feedback from others. I'd like to know if you feel using MS auto-increment field is a good solution these days or should one grow their own ? Thanks, Me.
3
3583
by: Poul Møller Hansen | last post by:
Hi, I need an auto incrementing field that will contain values like N000001, N000002, N000003 etc. I think the way is to use the value from an identity field in a stored procedure that is triggered at insert. I can't see that it can be made in pure SQL, but Java is not a problem. Any of you that can tell me the way of doing it ?
4
6461
by: Phillip J. Allen | last post by:
Hi all, I have a table with an “autonumber” primary key field that also acts as a foreign key in 2 other tables. I would like to programmatically add a new record to the first table and the other 2 child tables. The reason for doing this is to copy one complete record (parent and child table records) into a new record so the user can make a few modifications. So how do I determine what will be the next auto-generated number for my...
5
4557
by: Geoff Cayzer | last post by:
At http://www.blueclaw-db.com/tips_tricks.htm I came across a section which is included below and was hoping for some comment on the article. -------------- Almost never use this auto-number field as the primary key of the table. If you are thinking of hiring an Access programmer or consultant ask
3
2108
by: peddie | last post by:
Hi, I have a table that contains two key fields. I would like to assign the auto number for the secondary key field by starting from 1. For example primary Key id second Key Id ------------------ ----------------------------------------- 1 1 1 2 1 3
4
10254
by: Shahar | last post by:
Hi I need to get a field name 'ID'(that is an auto-number field) right after I add a new row to table, it's work like that: myCommand.ExecuteNonQuery(); myCommand.CommandText = "SELECT @@Identity"; // the auto-number fiels int iId = (int)myCommand.ExecuteScalar();
6
5075
by: Alpha | last post by:
I retrieve a table with only 2 columns. One is a auto-generated primary key column and the 2nd is a string. When I add a new row to the dataset to be updated back to the database. What should I do with the 1st column ? (Below I have a "1" in place for now). Also, Does the datase.AcceptChanges(); updates the changes to the database? Which command do I use to update the changes in dataset back to the Access database table? Thanks, Alpha...
2
8714
by: alexs | last post by:
Chaps, I'm starting to play with db2 V9.1 and am writing a stored procedure to manage accounting records from oiur RADIUS server. I've got an XML aware table with an auto increment primary key, a few sql columns and an XML column. For every XML object I'm inserting, i'd lke to have an ID attribute that has the same value as the SQL primary key that'll be created upon executing the insert.
2
2139
by: bubblegirl | last post by:
Hi, I need help in getting this database to work well (user-friendly) by generating auto client ID. The ID is NOT the auto number that Access automatically create when there is no primary key. For example: 1/1/2006 ; 2/1/2006; 3/1/2006 The first number represent the client number and, the second number represent the month of January and, the last number represent the year
0
9825
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10558
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10600
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10257
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9387
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6981
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5651
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4022
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.