Post reply

adding rows to a table with an identity column

  • March 17, 2006 at 6:53 am

    #627296

    For example?

    _____________
    Code for TallyGenerator

  • March 17, 2006 at 7:01 am

    #627300

    You're kidding me  

    Height, weight, shoe size, religion, date of birth, birth place, marital status, penis length...

    Need I go on?

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • March 17, 2006 at 7:08 am

    #627303

    Gender, customer number, marital status, social security number, drivers license number, felon, religion, height, weight, eye color, hair color, VIP, MVP, CPA, CAP, PCA, CPIM, MCP, A+, CLU, favorite food, date of employment, next reveiw date, etc. etc. etc.

    It all depends on the application, the instance, and the degree of data independence desired.  All of these elements could be considered independent attributes of a person, and would fit BCNF. Depending on the application and the database, many could also be normalized to separate table, if more information on the attribute was desired. For instance, if the application desired to keep date of MCP exam, type of exam, and grade, that should be normalized to a separate table.

    Also, passport does not identify you completely or accurately. You cannot pay for a restaraunt meal with a passport.  Even with a credit card, if the cashier wants additional identification, a passport will not work.

    Bottom line, there are no absolutes.



    Mark

  • March 17, 2006 at 7:15 am

    #627304

    Absolutely!

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • March 17, 2006 at 7:29 am

    #627309

    Gender - yes, No.1;

    customer number - what is this? Customer of what?

    marital status - how many of them?

    social security number - it's not a personal property, it's reference from the person to Social Security System in USA;

    drivers license number - which one?

    felon - you mean records about convictions in some book?

    religion - which one?

    height, weight, eye color - body's properties, not steady, cannot be used for personal identification;

    hair color - tell women about that

    VIP - for whom? It's relation between the person and some social institution;

    MVP, CPA, CAP, PCA, CPIM, MCP, A+, CLU, - the same;

    favorite food - hmmm ;

    date of employment - which one? Again it's relation between Person and Organization;

    next reveiw date - review of what? Employment Contract? So, it's property of the Contract.

    etc. etc. etc.

    Only one so far.

    Everything else - relations between Person and other entities, including other Persons.

    _____________
    Code for TallyGenerator

  • March 17, 2006 at 7:41 am

    #627313

    Everything else - relations between Person and other entities, including other Persons

    And that is the point.  It depends on the application the database is designed for.  A person table in a companies customer database would definitely have a customer number.  VIP could mean a flag that they have a special designation that determines special processing or credit terms or whatever.  It is all relative (important in a relational database ).



    Mark

  • March 17, 2006 at 7:57 am

    #627319

    Point is:

    changing of Customer Number or becoming a VIP does not make you another Person. Your PersonId suppose to stay the same in any system.

    And if you update a record for Person with a new Customer Number you destroy information about previous orders of this Person, because you wipe out his/her Customer Number relevant for those orders.

    So, your model is wrong and cannot handle real cases.

    Property of RELATION must be property of a relation, not property of Entity.

    _____________
    Code for TallyGenerator

  • March 17, 2006 at 8:26 am

    #627348

    Your example is wrong.  If the system was an order system, then the orders would (likely) be linked to a Customer table, not a Person table. If the Person table was in a CRM system or sales prospect system, having customer number on the Person would be an attribute of the person, where changing a customer number would not make you a different person, but link you to a different customer entity.

    The model is relative.  It will work in some systems, not in others.  Data is not, and never is, absolute.



    Mark

  • March 17, 2006 at 8:33 am

    #627349

    You can use the following Query :

    But in this case you have to use all the columns of Table A while inserting data into Table B

    insert into tableB

    select <Column names> from Table a

     

    *Note : You have to specify all the columns exept your identity column in TableA.

     

    Regards,

    AMIT GUPTA

     

     

     

     

     

  • March 17, 2006 at 8:42 am

    #627358

    My example is right.

    You were trying to assign Custome Number to Person entity.

    I told that Customer Number belongs to another entity.

    Now you agree that Customer Number is a property of Customer, not Person.

    If we put all your other "person properties" on its places you'll relise that you never really need more than 10 columns to identify an instance of any entity.

    That what I am trying to explain from the beginning.

    _____________
    Code for TallyGenerator

  • March 17, 2006 at 8:53 am

    #627365

    I agree that you rarely need more than 10 columns to identify an instance of an entity but what about attributes of that entity?

    Would you propose something like this to represent an attribute of the person representing his state of birth?:

    Create Table Person(PersonID int)

    Create Table State(StateID int, Name varchar(50))

    CreateTable StateofBirth(PersonID int, StateID int)

     

    I believe this is overly complex and this is also a proper design:

    Create Table Person(PersonID int, PersonID int, BirthStateID int)

    Create Table State(StateID int, Name varchar(50))

  • March 17, 2006 at 9:01 am

    #627368

    What about me?

    I was not born in any of states.

    Can you make a record about me in your system?

    _____________
    Code for TallyGenerator

  • March 17, 2006 at 9:10 am

    #627369

    Sergiy:

    Now you agree that Customer Number is a property of Customer, not Person

    NO, I did NOT agree.  I said (repeatedly), it is all relative to the system you are building.  In some cases, a Customer Number is a key; in others it is an attribute.  It is perfectly proper BCNF normalized form to put attributes of an entity in the same table, if there are no other related attributes on that table.

    Jeff:  Some would say (and I would agree) that having a foreign key to a table with a single data column is overnormalized.  It is perfectly acceptable to have the state name in the person record, instead of a stateid that only points to a state name. (This is one of my favorite examples of overnormalization, and of distinguishing "academic" DBAs versus real-world application designers.)



    Mark

  • March 17, 2006 at 9:16 am

    #627371

    Hopefully not.



    Mark

  • March 17, 2006 at 9:18 am

    #627373

    Maybe my system is modeled so that the only people in the database will be ones born in the US, say a database of all births for a hospital corporation operating in many states.  But you could add States with values for 'Foreign', 'Unknown', 'Not applicable' or something like that if you want to be part of my system.

    But you are missing the point, what about attributes?  What if I changed my example from BirthState to BirthCountry?  Surely you were born in a country.

Viewing 15 posts - 16 through 30 (of 40 total)

You must be logged in to reply to this topic. Login to reply