CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2012
    Posts
    9

    How to Make an Entry in a Table and Do it in Another

    Well i am currently making a system using ASP.net C# and SQL Server

    What i want to achieve is when i create an entry in a Table. Lets say "Customers" with primary key "customerID".

    When i create entries in the table and i have a Column with Identity and i successfully entered every data needed.

    I want to create an entry in another table with the same value as my primary key.

    So in the first table

    Customers
    customerID: 1
    Name: Mr. Random
    Pass: RandomPassword

    it will create an entry in another table like this and the other data are empty.

    CustomersOrder
    customerID: 1
    order1: <empty>
    order2: <empty>

    i want to do this because i separated some data on tables and i want them to have the same key or ID to search or edit them easily.

  2. #2
    Join Date
    Jul 2012
    Posts
    90

    Re: How to Make an Entry in a Table and Do it in Another

    The way this is normally accomplished is using Foreign Key constraints in the database e.g.

    Customers
    customerID: (generated by database as primary key identity)
    Name: Mr. Random
    Pass: RandomPassword

    CustomersOrder
    orderID: (generated by database as primary key identity)
    customerID: (the value from customerID in the Customer table)

    OrderedItems
    orderedItemID: (generated by the database as primary key identity)
    itemID: (This will point as a foreign key to your items table where you get the details of an item - description, price, availability, etc.)
    orderID: (foreign key tying to order)
    quantity: (number of this item included in this order)

    You create a foreign key constraint on the customerID column of the CustomersOrder table tying it to the customerID column of the Customers table

    This allows the database to ensure that you do not have an order that is not associated with a customer. It also ties order records to customer records in a one to many way (each customer can have many orders). Now for your searches, you can join the tables in the from on customerID.

    Orders also have a one to many with items (an order can have many items).

    This is known as "Referential Integrity", it alows the database to ensure that if a record in one table "refers" to a record in another table, that the record being referred to exists.
    Last edited by CGKevin; November 3rd, 2012 at 11:07 AM.

  3. #3
    Join Date
    Oct 2012
    Posts
    9

    Re: How to Make an Entry in a Table and Do it in Another

    genius, i was already there, but lets say i have 2 tables

    table 1 with primary key
    table 2 with no primary key but set a foreign key to table 1

    is it possible that when i input data on table 1 and generates a value in its primary key, it will automatically create the record on table 2 with the same value from table 1 primary key

    like

    table2foreignkey=table1primarykey, even though i haven't filled in the values in table 2

    a visual should explain better



    This is the table with the primary key and has identity set to 1 seed. When i fill every data in there
    i want its primary key value (in this case 1) to be the same value of the foreign key in table2 even without me filling up the values there.



    i haven't set this foreign key to have identity
    Attached Images Attached Images  

  4. #4
    Join Date
    Jul 2012
    Posts
    90

    Re: How to Make an Entry in a Table and Do it in Another

    First, I would recommend that you have a primary key column on all of your tables. This allows for deeper nested referencing as I demonstrated above and also primary keys are automatically created with clustered indexes for better performance in your searches.

    Second, you could accomplish what you are asking using a trigger on the Customer table, however, I fail to see the advantage in creating an order for a customer before they have actually ordered anything.

  5. #5
    Join Date
    Jul 2012
    Posts
    90

    Re: How to Make an Entry in a Table and Do it in Another

    Normally, the way these things work is...

    Customer logs in - during the authentication you go to the database and get the customer information.

    Customer wants to create a new order - you use the customerID you obtained during the authentication process to create the new order (write it to the foreign key of the orders table).

    Customer selects items to order - you use the orderID obtained while creating the order to write records to the orderedItems table.

    Now you have items associated with an order that is associated with a customer.

    The process repeats next time the customer needs to order something.
    Last edited by CGKevin; November 3rd, 2012 at 11:28 AM.

  6. #6
    Join Date
    Oct 2012
    Posts
    9

    Re: How to Make an Entry in a Table and Do it in Another

    well typically its not going to be used as a customer system.

    its a hospital system

    when a new patient comes in the hospital gets necessary data like name and etc but they dont need data like diagnosis/cause of death
    and etc.

    so those info that are not needed in the initial input, i separated in another table, so the reason i want to link those 2 table is to make sure that when the info gets edit/updated, i can easily locate it using the primarykey in table 1 to any other table.

  7. #7
    Join Date
    Jul 2012
    Posts
    90

    Re: How to Make an Entry in a Table and Do it in Another

    Then my reccomendations stand. I would create an insert trigger on the customer table that on insert, creates a record in the orders table using the primary key value from the newly created customer record.

    All of the info needed to accomplish this will be in your SQL Server Books Online.
    Last edited by CGKevin; November 3rd, 2012 at 11:41 AM.

  8. #8
    Join Date
    Oct 2012
    Posts
    9

    Re: How to Make an Entry in a Table and Do it in Another

    ill try it tomorrow, thankyou very much, im from not from us so its like 12:30am here. Thankyou very much for your help man.

  9. #9
    Join Date
    Jul 2012
    Posts
    90

    Re: How to Make an Entry in a Table and Do it in Another

    In a hospital setting, you are going to accumulate a lot of records over time, you should create indexes on the table columns that you use a lot in joins and in your where clauses to improve query performance. If you don't, you'll start eventually getting timeouts because your queries will start taking too long to run. Also, setup a job to re-index your tables once or twice a month.

  10. #10
    Join Date
    Oct 2012
    Posts
    9

    Re: How to Make an Entry in a Table and Do it in Another

    can you further explain this indexing? i was also considering the speed in query when i have lots of data in the database.

  11. #11
    Join Date
    Jul 2012
    Posts
    90

    Re: How to Make an Entry in a Table and Do it in Another

    Just Google "SQL Server Table Indexing" there is lots of information on the subject.

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured