473,320 Members | 1,948 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Writing to a relationship based table

147 100+
Hi

I think I'm just after advice on best practice with this!

I have a database which has 2 tables. The first table has ID, Name, Address and the second table has ID and JobType.

Table 02 can list multiple types of JobType so we could have

Table01:
1. D Smith. Address01
2. S.Jones. Address02

Table02
1. Accounts
1. Sales Ledger
1. Chartered Accountant
2. IT
2. Web Design
2. Graphic Design
2. 1st line support

A join gets them together and all works perfectly.

However, the content will be updated when some one fills in a web form. Do get the form to send results to both tables, I assume I have to open the database twice or can I open it once and open each table one at a time? I know I could practice this and not ask the question but I am interested to know what is the most efficient way.

I would also need to know what ID to use. So when I populate Table02 it uses the correct ID from Table01. At the moment Table01 ID field is incremented automatically. Would you suggest I always assign the ID myself, or would I need to query the database, find out what ID number is currently in use and then increment it by 1 and assign the new number to an ID variable which then populates the Table's Field? In which case, am I opening the database again or trying to perform all 2/3 stages in one go?

Thanks

Dave
Feb 15 '10 #1
3 1568
Frinavale
9,735 Expert Mod 8TB
You should always close the connection to the database when you're finished with it. Do not open a database connection and leave it open after you have sent the page to the client (aka the web browser). If the user walks away or closes the browser...then there is a very good chance that that database connection will remain open. There are some major problems with this and it could cripple your server resources and/or your application. Look into connection pools and database connections to get a better understanding of what I'm getting at here.

I think you should also look into using stored procedures. If you worried about how many times you're opening and closing connections to the database to do your processing, these can really help you. This has nothing to do with ASP.NET; you do this in your database.

To give you more help on the topic of assigning unique IDs, it would really help if you mentioned what type of database you were using....SQL Server? MySql? Oracle???

-Frinny
Feb 16 '10 #2
Curtis Rutland
3,256 Expert 2GB
Ok, I think you basically have two questions:

1) Where should you generate the auto-id at? DB or client?

2) If DB, how do I get the generated ID to use in a related table?

You do want to allow the DB to generate your auto id's for you. I've run into a similar situation, and tried the idea you had (finding the current max id, incrementing it and using that) and I ran into some issues, namely simultaneous updates creating the same id number.

Then I discovered the SCOPE_IDENTITY() system function of T-SQL. This is assuming that you are using MS SQL Server, but I believe that most RDBMs have similar capabilities.

You can write a stored procedure to update table 1 and return the SCOPE_IDENTITY() value, and use that value to update table 2.

As to opening and closing connections, it depends on how you are doing it. For example, if you are using LINQ, it will automatically manage connection states for you. If you are doing it manually, I would suggest opening the connection, running both update queries, then closing it. No need to open/close/open/close when you're performing sequential operations. Don't leave it hanging open though. Close it when you're done with the current set of operations.

Hope that helps.
Feb 16 '10 #3
DaveRook
147 100+
Hi Frinavale and insertAlias

Thank you both for your suggestion.. I will go and read up on Scope_Identity

I don't understand how I could use stored procedures in this instance as the informtion to be written on the fly as it depends on what the user enters as to which fields in the database do/don't get updated.

May be I have mis-understood stored procedures and will also re look into this.

I know LINQ is a fairly new langauge but if it manages connection states automatically would it not be better for me (some one still learning) to continue with SQL as part of my education to ensure I understand the ado.net procedure?

Thank you both, as always, problem solved :)
Feb 17 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Alcyone Oliveira | last post by:
------=_NextPart_000_000D_01C36572.F6480CE0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_000E_01C36572.F6480CE0" ------=_NextPart_001_000E_01C36572.F6480CE0 Content-Type:...
9
by: Bob C. | last post by:
I want to create a 1-many relationship. Parent table has a primary key, child table has no primary key. The child table does have an index with all four fields of the parent's PK. How can I do...
2
by: Todd D. Levy | last post by:
I have a primary table (containing basic contact information) and a number of subsidiary tables (containing various additional information) for employees. Most of the subsidiary tables have a...
3
by: Leinad Kong | last post by:
I'm using Access 2002, as front-end and back-end as well: 1) I faced database corrupted problems, when more than 1 user edit concurrently. I'm using All-records Locking, and open-exclusively as...
8
by: jquest | last post by:
Hi Again; I have had help from this group before and want to thank everyone, especially PCDatasheet. My database includes a field called HomePhone, it uses the (xxx)xxx-xxx format to include...
2
by: Ryker | last post by:
I think I am having problems with table relationships. Here is what I have. Table 1 has Social Security Number First Name Last Name Autonumber Since I will have many duplicate Social...
3
by: shawnews | last post by:
Ok...I'll first describe briefly what I've done. Working from a paper form with over 200 fields - broken into 10 areas, I created a database with 10 tables. I then created a form using those 10...
1
by: Phive | last post by:
Hi all, What I'm trying to do and having a lot of trouble with is pulling how one user is related to another user from my database. I'll explain... The table (relationships) looks like this: ID...
2
by: access baby | last post by:
I have 5 tables need to create relationship Customer Table CustomerID(auto number)(primary key) Customer Name(text) Customer City(text) Customer St(txt) Customer OrderTable Customer...
11
by: RobertJohn | last post by:
Hi all I am using Access 2007 to start a small home library application, and so far it has two tables. 1. Books, with fields Book_ID (Primary Key) and Title, and 2. Authors, with fields...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.