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.
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.
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.
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.
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.
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.
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.
* The Best Reasons to Target Windows 8
Learn some of the best reasons why you should seriously consider bringing your Android mobile development expertise to bear on the Windows 8 platform.