Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2010
    Posts
    12

    Primary key multifield incremental¿how implement?

    Hello all, i´m a newbie in database design and i want implement a multifield incremental primary key for my project. The primary key have the next composition OXXABC000N, where O means Order, XX is the last numbers of the year, ABC is the product's category, for example fruit is FRU and N is a incremental number of a category's order. When a customer request a order, fill the order form and push submit the system generate a primary key PXXABC000N incremental for the category.

    P10FRU0001
    P10FRU0002
    P10VER0001
    P10FRU0003
    P10VER0002

    ¿How implement this primary key in the table?¿the INT will be the primary key?¿the PXXABC000N? I don´t know.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tortiman View Post
    ... and i want implement a multifield incremental primary key for my project.
    could you explain why you want this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Posts
    12
    Quote Originally Posted by r937 View Post
    could you explain why you want this?
    to identify a customer's order. This code identify the order.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, a simple integer primary key would identify a customer's order

    what i was asking was, why does it have to be such a complex code?

    which is not, if you haven't guessed by now, a very good idea...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2010
    Posts
    12
    Do you haven´t see a code like this? This type of codes are very normals. In my case with this code you know the year and the category of the order.

  6. #6
    Join Date
    Oct 2010
    Posts
    12
    Another example, a garage where it have 15 mechanics repairing cars. Each car must have a job order that is assigned to one mechanic, the jobs order could identify for a chain like this OYYYYMMDDAA0000N, where O is a string first letter of Order, YYYY is the year, MM is the month, DD is the day, AA is the number of mechanic and 000N is a incremental number of each mechanic.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tortiman View Post
    Do you haven´t see a code like this?
    numerous times, when people post questions asking how to achieve it

    Quote Originally Posted by tortiman View Post
    In my case with this code you know the year and the category of the order.
    that's a bad idea because if the year changes or the category changes, then the primary key has to change

    my advice is, don't do it

    if you do not know how to achieve it, i am sorry, i will not be able to help you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This sort of code is very common in business. What is far less common, and is what Rudy is concerned about, is:
    1. Autogenerating it
    2. Using it as the Primary Key (we assume by that you want to use it as a referenced column in foreign keys)
    So, Rudy is not saying that to have something like this is wrong, but it would be wrong to reference it because of the danger he points out - that since there is so much information in the code there are a lot of things that could change. A referenced key column should typically be immutable (unchanging) where at all possible.

    Ultimately, however, your question is how to generate an incrementing numeric value over a group. That depends on your RDBMS and is not really a database design concept.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2010
    Posts
    12
    Quote Originally Posted by pootle flump View Post
    This sort of code is very common in business. What is far less common, and is what Rudy is concerned about, is:
    1. Autogenerating it
    2. Using it as the Primary Key (we assume by that you want to use it as a referenced column in foreign keys)
    So, Rudy is not saying that to have something like this is wrong, but it would be wrong to reference it because of the danger he points out - that since there is so much information in the code there are a lot of things that could change. A referenced key column should typically be immutable (unchanging) where at all possible.

    Ultimately, however, your question is how to generate an incrementing numeric value over a group. That depends on your RDBMS and is not really a database design concept.
    Sorry, but don´t understand, my english isn´t good. Do you want say that use a multifield incremental primary key isn´t a good idea? That this type of key is a business key and the business keys aren´t primary keys. What type of key use yours for the example of the order job´s garage.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tortiman View Post
    Sorry, but don´t understand, my english isn´t good.
    Yes I'm sorry - I didn't write that very clearly. I have to go out but will reply properly tomorrow.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Oct 2010
    Posts
    12
    What do you think about this article? What type of primary key use you? In the relational theory isn´t allow the autoincremental primary key.

    How to choose the primary key of a table
    Tema: Oracle Programming SQL
    Mellado, 23 February, 2007 - 22: 20

    I sometimes get the impression that choose the columns that constitute the primary key of a table is a decision which is usually taken very lightly, when the reality is that the correct choice of primary keys is a decisive factor in order to build a relational database model well formed, coherent, maintainable and easily scalable. The irony of the matter is that it is actually a very simple task if We understand that no value taken from the domain you are trying to model used to uniquely identify records in database. Said another way, can not be used as primary key a column that contains information that has meaning for users; We must create a column explicitly to the primary key. Or put a third form, if you create a table of people not use ID as the primary key.

    But why is not a good idea to choose ID as the primary key?, what does that number not identifies us uniquely?. Yes, but emphatically NO. Consider for example that minors or citizens of other countries typically do not have an identity card, and all this without mentioning the fact that the ID numbers are not unique, historically the same numbering is has reused in different people.

    You can currently be thinking in very smart alternatives to solve the problems posed in the preceding paragraph, possibly through the incorporation of new columns in the primary key, but believe me, leave it, the effort will be useless, the reality is that problems will not go away, will increase. For example, we can think that we can store minors of age in our table if we add a new column to the primary key that will indicate if the ID is really yours or his legal representative. But it happens that a person can have several children, thereby need another new column to distinguish them. We also think that we can store people from different countries if we add a new column that will indicate the nationality of each person, thereby also the same number repeats for people from different countries. But it happens that a person may have several nationalities. And so, back to start. It is normal that following this line of reasoning has just need easily 7 or 8 columns.

    In general lines do not use primary keys composed in master tables, i.e. formed by multiple columns in tables that store the basic entities that manages a system key. Complicated models making them difficult to maintain and extend. Think in the example above, if follow in our original idea of using ID card as a primary key, so that tomorrow we were a problem of the aforementioned and in addition bridge it by adding a new field to the primary key. It would happen that would have added that new field to all tables and processes which would refer to the table of people. The change would not be unique, localized, whether or not that is expand as a contagious disease by all entities and processes on your system that would have some kind of relationship with the table of people.

    Be careful with composite keys, especially because sometimes occur camouflage under the appearance of what is normally referred to as "smart codes". An intelligent code is the Union of several keys in a single column, or a primary composite key with lamb skin. Typical of this type of code are pagers, like a film entry in that contained "S07-F12-B08", which would mean "7 Hall" (S07) "12 row" (F12) "8 chair" (B08). This type of composition is sometimes used to identify entities due to its location, assuming the fact that may only be an entity in one place at the same time. The main problem with this type of primary key is that sometimes, in the real world entities are misplaced and end in locations other than their codes, which indicate being necessary to change the primary keys in the parent table and on all tables that are referenced, which can be quite laborious, not to say cumbersome and prone to errors.

    Above gives us the last two definitive tracks to understand the why there is no use ID as the primary key table of people. The first track is a primary key can never rely on information that you enter manually. If you rely on a primary key value of that type a user is creating a brutal unit with the inner workings of the system. Coupling between layers of an application always must be weak, so that you can edit each component individually without affecting the rest, and accordingly, the rules that govern the real world do not have because influencing those laid down in the interior of an application. If the primary key table person outside DNI, and tomorrow you decided to use tax, then would have to change the entire model and processes to manage the key as an alphanumeric field instead of as only numeric. If ID is not key then enough change that specific field alone.

    The second track is that a primary key has never be modified. The primary key of a record identifies an object within the domain that is modeling uniquely. If you change the primary key of a record a "mutation" by which registration passes to represent another object occurs. This aspect is perhaps a little subtle understand, especially by involves some level of abstraction. A person could say that it is the sum of its individual characteristics, as well as the place of any dimensions that casts (and possibly its absence in the dimensions that is not planned). It is an entity, a being unique and distinguishable. Its primary key has to identify it uniquely, if you change would represent another entity, not to the original individual. Think in terms of variables, such as those typically used in any programming language. First defined, and then can change them their contents in the same way that a person can change of clothing or hair. Variables always refer to a same concrete information, regardless of the value contained in a given time. Persons similarly, same hairstyle or clothing to relocate, and even where we are on top of that we remain ourselves, not others. If a person you changing your identity card because it introduced a number wrong discharge, should continue to be the same entity that was originally inserted in the system, not another involving a massive upgrade of relations.

    Ultimately, the correct way to identify entities in a table is to use a code or unique identifier that has no meaning in the real world. A simple numeric value generated sequentially is sufficient. The idea is to create a table with a first column of numeric type to serve as a primary key. In the initial example, people, table could be something like: ID_PERSON NUMBER (10) NOT NULL. So when you insert a first person on the table to be assigned as the primary key value of 1 (or 1527, is indistinct). When you insert a new person assigned the 2 (or 1528). And so on. In this way there is no possibility of conflict or what is same, encountering errors by duplicate primary keys. Each person is paired with an identifier that uniquely distinguishes from the rest. And that person serves for any type of entity. An engine is not identified by its number of rack, but by its primary key. A television is not identified by its serial number, but by its primary key. An invoice is not identified by their number, but by its primary key. A user not identifies by name, but by primary key. Capture the idea?

  12. #12
    Join Date
    Oct 2010
    Posts
    12
    continue....

    This way of defining keys often create considerable confusion in the developers not accustomed to this way of modeling. The first issues that typically arise are related to performance, especially as it searches for columns such as identity card are quite common. To cease to be part of the primary key must define additional indexes on the tables for these columns. Some designers abjure spontaneously when it is suggested to add multiple indexes on a table alleging that it corresponds to a bad design. But then, for what the heck want indexes. Another common issue that is often raised is how to generate primary keys. The answer I think it largely depends on the tools used to develop. Oracle provides such objects of type SEQUENCE, that generate sequential way atomic values. Some bootloaders allow to indicate that the columns are AUTO_INCREMENT, so that every time that a row is inserted automatically generates a new value. The "gurus" are usually wanders in aspects such as whether it is necessary to use a single script to all entities of the system or a separate script for each table. What is clear is that never runs COUNT or MAX on the tables to get the next sequential, are direct attacks on the integrity and performance of the application. Another issue that is often raised is the theme of the size required by the primary key column. A size 10 represents ten billions of possible values, enough for most general-purpose applications. What needs to be done above all is that all tables have a primary key with the same size, not each with a different size adjusted its estimated occupation column. Concerning this last point, we must clarify that add a size ten column does not automatically add 10 bytes per record, the required extra space depends entirely on Manager database that you are using.

    Working with tables defined this way for the first time can be somewhat disturbing at first. It may seem to maintain consistency of so many identifiers is rather complicated, but it is not, in fact, today it is the naturally design and work. Simplifies design layers persistence with which objects in memory, map are available at the time that helps separate the information that is significant for users of the form is handled internally. If your tools do not support this way of working will seriously thinking to replace them by new ones.

    And last but not least, did not want to leave comment on the fact that surely all these arguments appear unnecessary complications for small systems in highly controlled environments. However, think so is forgetting a maximum computing we should always bear in mind: always requirements change!

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tortiman View Post
    What In the relational theory isn´t allow the autoincremental primary key.
    wha?? why not?

    please explain
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Oct 2010
    Posts
    12
    Quote Originally Posted by r937 View Post
    wha?? why not?

    please explain
    Because the experts say that the use the autoincrement primary keys don´t until the tables are normalized.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tortiman View Post
    Because the experts say that the use the autoincrement primary keys don´t until the tables are normalized.
    this is quite good advice -- don't use autoincrements until normalization is finished

    however, your comment "In the relational theory isn´t allow the autoincremental primary key." is not correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •