January 26, 2011 at 8:16 am
Hi Friends,
Hope u are doing well. I am DBA but have very basic knowldge of T-SQL. My problem is as follows,
I have 3 tables:
Table A
=====
book_id int
book_name varchar(20)
author varchar(20)
Table B
=====
book_id int
avl bit
Table C
======
book_id int
Sale Price mony
date datetime
avl bit
It means that table A is my main table and table B is my book avilability table and table C is my transaction table. Now i want to update my Table B automatically. Whenever my books avlability is change.
How i can do this can any one help me please.
Thanks in Advance.
________________________________________
M.I.
[font="Times New Roman"]
January 26, 2011 at 8:20 am
you could do this via a trigger or modify the stored proc that is used to insert data to table C to update table B as well.
Also, what is the purpose of Table B? from the brief detail you provided, it seems that it would be better off as a view on Table C. this would have the advatage that you would not have to worry about keeping it up-to-date
January 26, 2011 at 8:34 am
I agree. I can't see why you would need to join to that table unless there are other fields and functions you're not showing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 26, 2011 at 8:38 am
See my tables are,
Book table
=========
book_id int
book_name varchar(20)
author varchar(20)
Availability table
==========
book_id int
avl bit
Transaction Table
=========
book_id int
Sale Price mony
date datetime
In table B i want to maintain avlability of the books. Not only I have this table. I have more tables and each table have a avlability flag. So i want to maintain it in a different table.
________________________________________
M.I.
[font="Times New Roman"]
January 26, 2011 at 8:48 am
What happened to the available bit in Table c?
The thing is, unless you're maintain a history of availability so that all entries to Table B are inserts (in which case you should add a version column or a updatedate column), there doesn't seem to be a reason to separate the availability of the book from the other properties about the book. It's not a separate entity in the design otherwise.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 27, 2011 at 5:19 am
Hi Grant Fritchey,
I have multiple table one for magazine, one for books, one for cdvd and so on. Each table have a flag of availabilit. to write in every table the same 'avl' column quite difficult to access from the transaction table. For taht i separate the 'avl' table to easy retireve.
For example: i have data in all the tables, like magazine, book and cdvd table and i lend one book to person A. Now i will go to update transaction detail in transaction table, so now avlability flag of that book will be 0. It is very easy to do in this manner if i kept avlbility column in all tables so its quite difficult to maintain in transaction table. Transaction table column will increase in this case.
And some magazine have books and CDVD's also, some CDVD's doesn't have magazaine or books. Otherwise i can maintian it in a single table no need to separate it.
Is there any soloution to Asynch this column on the effective table column?
________________________________________
M.I.
[font="Times New Roman"]
January 27, 2011 at 8:08 am
I'm sorry, I realize I must be thick, but it's still not making sense to me. You're saying that maintaing the availability column in the table for a book, ccdvd or whatever, is harder to do in the table that has the book information, so instead you're breaking that into a different table where you're maintaining it? Does that mean if you have a book, a movie, and a magazine table that you also have a bookavailability, movieavailability, and magazineavailability table? That's a lot to maintain.
If, on the other hand, you're saying that you have a transaction table that maps to all the other tables, you might have a design that's problematic because I suspect you've got what's called an arc relationship, meaning for this row it can be related to book, but not movie or magazine. In this row it can be related to movie, but not magazine or book., etc. But in each, it's entirely possibly to related to all three. Again, that's a rough design. In that case you'd be better off having a table called Item or something, and the Item table has fields that are common to a book, a magazine, and a movie, such as availability, and then the Item is of type, movie, etc., and links to a table of that type. this is easier to maintain.
But again, since I'm confused by all this, I might be off.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply