Re: Please help with Inserting ID into table from another table in Oracle 9.2

On May 1, 4:42 am, MrHelpMe <clintto..._at_hotmail.com> wrote:
> On Apr 30, 3:08 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
> > On Apr 30, 12:38 pm, MrHelpMe <clintto..._at_hotmail.com> wrote:
[]
>
> > > Sorry DA Morgan let me clarify. I'm not sure how much you know about
> > > front end coding but I will keep it simple. I have an ASP page that
> > > when the user clicks on the page he is asked a serieds of question.
> > > Like what is your first name, what is your last name, what is your
> > > email adress, what kind of equipment do you want, for what purpose
> > > etc. After the user fills in all this information he/she will click a
> > > submit button. When they click the submit button, behind the scenes I
> > > do an insert into(based on the insert statement that I showed you
> > > above) into the users table, hardware table and approver table. Now
> > > if Joe Smith fills out this form and then clicks submit and then comes
> > > to the page again, fills out the form again and clicks submit, well I
> > > would not want to insert his name twice into the users table but
> > > rather have unique users being captured in this table. So therefore,
> > > how do I code this in the backend to say if the user is not unique
> > > don't insert the entry into the users table but all the other info.
> > > that he/she submitted will be inserted into the respective tables. I
> > > hope this is clear. Please let me know if it is not and in the mean
> > > time I will have a look at what you mentioned
>
> > Such is the folly of ID columns as the primary key.
>
> > Change your data model!
>
> Sorry guys I am still a bit confused. I was hoping for my primary key
> to be the next value in oracle. This is an auto incremented number.
> DA morgan what would the select count(*) do for me and where would I
> include this(before the insert). Sorry I am just a bit confused as I
> have never done this before.
>
> Ed Prochak could you give me a sample idea of the data model. I have
> no problem in changin it as I want to build this application right.
> Sorry just confused on the answers. One is saying to change the data
> model and the other to add a select count.

Daniel has addressed the question of using count(*).

I just will add a little about the datamodel. Try to remember this phrase:
the key, the whole key, and nothing but the key, so help me Codd.

There comes a point where a pseudokey is useful, but new DB developers often rush to using one rather than going thru the work necessary to identify a true Primary Key. Take a step back from tables and columns. Go back to a higher, logical layer, like an ERD. Ask yourself: exactly what entity am I modelling here? what makes it uniquely identifiable (IOW what are its key attributes). For example your User table has both name and email attributes. Can a user of your system have more than one email address? If so, are they treated as different users? Then maybe email is the Primary Key (PK). Or is the user a single person? then maybe firstname/lastname forms the PK. Or based on one of your comments,
>>>>> ... if the FirstName, LastName and Email address are the same do
>>>>> not insert into the Users table ", then those three form the PK.
 Once you have a proper PK then you will never have duplicates. You don't need User_ID to be the PK.

 But if you use a pseudokey, like "ID", then you might get duplicates easily. Not all agree with me, but I claim that a data model like yours, converts a powerful Relational Model DBMS into a Network Model DBMS. You abandon the power of the database in favor of maintaining the connections between entities in the application code.

So as Daniel suggests, get some on-site help from a more experienced developer. Talk to your DBA. If appropriate, take a class on relational database design. (If you happen to be in the Northeast Ohio area, I might be available to help a little. send me an email if it seems reasonable.)

Daniel and I are not being rude. We are sincerely trying to help you to fish.

  Ed Received on Tue May 01 2007 - 15:08:35 CDT