check record before inserting

[pelusa]

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.

[abev]

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.

[pelusa]

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.

[abev]

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.