473,320 Members | 1,857 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.

check record before inserting

9
i am trying to figure out the following:

i need to insert a row to table b where the customer_number can be the same as well as the order_number
the item_number will always be different (a spoon (item 123, a cup 345 can be part of the same order)

but if the ship date is the same (no time portion) then I just need to insert one row for this order.

this is table a

id order_number Customer_id item_number customer_name shipping_date
673 6176716 21608490 LP1279299 AMY BANNER 2008-02-08 11:07:01.000
674 6176716 21608490 LP1279298 AMY BANNER 2008-02-08 11:07:36.000

any suggestions will be great, thank you.
Feb 11 '08 #1
3 912
abev
22
pelusa what it sounds like is you are just creating simple master / detail tables. If thats the case lets call table "A" the master table and table "b" the detail table. Kind of like Invoice/Invoice Details.

Table "A" should NOT have item numbers in it. Keep your data normalized (google 'database normalization'). Even if the order only has one item its ok to have one master record and one detail record.

table A would have everything you have in it except for item_number. Table "B" would then have a DetailID(pk), ID(from Table "A"), Item_Number, and then anything else specific to that item like quantity, price etc.

HTH

i am trying to figure out the following:

i need to insert a row to table b where the customer_number can be the same as well as the order_number
the item_number will always be different (a spoon (item 123, a cup 345 can be part of the same order)

but if the ship date is the same (no time portion) then I just need to insert one row for this order.

this is table a

id order_number Customer_id item_number customer_name shipping_date
673 6176716 21608490 LP1279299 AMY BANNER 2008-02-08 11:07:01.000
674 6176716 21608490 LP1279298 AMY BANNER 2008-02-08 11:07:36.000

any suggestions will be great, thank you.
Feb 12 '08 #2
pelusa
9
i understand what you are saying and it makes sense.

i have one question after reading your post if you don't mind.

i made a mistake and actually i should have said that these are not item numbers but box numbers something like
order 2345 has 5 boxes.

would in this case still table a and the option to follow?

thank you.
Feb 12 '08 #3
abev
22
i understand what you are saying and it makes sense.

i have one question after reading your post if you don't mind.

i made a mistake and actually i should have said that these are not item numbers but box numbers something like
order 2345 has 5 boxes.

would in this case still table a and the option to follow?

thank you.
Definitely. Whenever you have a One-to-many relationship you should strongly consider two tables. I say strongly consider bc there could be a reason to keep it all in one table but I wouldn't do it . Even if it works for you today, your scalability is shot (in case you want to make the database bigger/expand your project).

So to your case: I absolutely would have 2 tables in a One-to-many relationship where Table A (Order Master) relates to Table B (Order Detail).

Its ok if your order detail table (in your case) only has a few columns in it. At first it may seem like a waste, but it is the proper form.

There is a lot to consider in database design and I have made every mistake and still make them, but nothing is worse that an incorrect design. You can always adjust your front end (web pages, forms) but once you have data in your database and need to change it, design changes are a nightmare.
Feb 13 '08 #4

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

Similar topics

3
by: Nick | last post by:
Is there a way to reference the 'primary key' field of a record you are actually inserting at the moment. Im using an 'int auto increment' field as the primary key. I could reference it with a...
7
by: Randell D. | last post by:
Folks, I've heard of buffer overflows being used/abused by hackers and believe one method to reduce this from happening is to check the length of my form data before writing it to my MySQL...
3
by: NuB | last post by:
I have an app that inserts records into a SQL db, I have a file that I'm reading to get the data out of, aka 'records', how can I determine of the record has been inserted into the database so i...
3
by: pjcraig | last post by:
This is driving me crazy! I have a form that a user will access from another form by selecting the item that they wish to view. When they open the new form, I pass through the id of the item they...
6
by: AA Arens | last post by:
Hi, I have a database with 2 main forms. Contacts and companies. I share the base with two others via LAN. On the companies form I have buttons to navigate throught the records (>400). We are...
1
by: zhidsuk | last post by:
i have an stored procedure that inserting record to table and with the scope_identity inserting the ID to another table but..some times (something about one record to 100) the first table miss the...
5
by: bob44 | last post by:
Hi, I recently created a mysql database using phpmyadmin. I then proceeded to make a form to insert data into the database, but the problem is that the form is only able to insert one record, and...
20
by: dav3 | last post by:
Alright folks I am in need of a lil guidance/assistance here. I have a program which reads in a txt file. This txt file contains lines of the form January 3, 2007, 85.8 Now each line of the txt...
3
by: milecimm | last post by:
Hi, I want to check in the below code if the email exists in the database before inserting. How can I do that? Dim EmailAlertDataSource As New SqlDataSource() ...
11
by: richardkreidl | last post by:
I'm trying to check for duplicates before I do an INSERT into the Access database table. I basically want to alert the user if the 'ProjectName' and the 'MileStones' are already in the table.. ...
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
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...
1
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: 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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
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.