Help with a new table design

[yaminids]

Hello friends,

Sorry for posting the question in this section as I couldn't figure out an appropriate section.

I am trying to design a table which is intended to hold data related to vendors.
Every vendor can receive one or more orders. The table should hold vendors along with orders received

For example:
Vendor A received 1 order, vendor B received 2 orders and vendor C received 5 orders

A-->ord1
B-->ord1, ord2
C-->ord1, ord2, ord3, ord4, ord5

What is the best way to design such a table?

Thanks a bunch in advance
Yamini

[Pagadrai]

A-->ord1
B-->ord1, ord2
C-->ord1, ord2, ord3, ord4, ord5
Yamini

Hi Yamini,
You can post such Questions in General forum.

Anyways, since the number of orders for each vendor is not 'fixed', you can have a generic table like

VENDOR_NAME | ORDER_NAME | ORDER_DETAIL
A | Ord1 | Order1
B | Ord1 | Order1
B | Ord2 | Order2

and so on..

This is my suggestion. Lets us see others' opinion.

[ray.wurlod]

Use a database that can handle multi-valued fields, such as UniVerse, Unidata, jBase, D3, etc.

[chulett]

Or use two tables. Do you not have a modeler where you work? I mean, someone who's official job it is to design the tables projects need there?

[vivekgadwal]

For example:
Vendor A received 1 order, vendor B received 2 orders and vendor C received 5 orders

A-->ord1
B-->ord1, ord2
C-->ord1, ord2, ord3, ord4, ord5

What is the best way to design such a table?
Yamini

Yamini,

If you are trying to build this table in a non-multi valued database (as Pagadrai was suggesting), then you need to have separate rows for a combination of each Vendor and Order (number or name??). You can have them as Primary keys and the rest of the details, if you have any, as attributes.

Code: Select all

VENDOR(PK)    ORDER NUMBER(PK)          ORDER NAME   ...
  A              123                     Order1
  B              234                     Order2
  B              345                     Order3
  C              456                     Order4
  C              567                     Order5
  C              678                     Order6

If it is an intermediate staging table that you are trying to create, instead of the modeler doing it for you, then, as Ray Wurlod pointed, use Universe database.

Let us know if this is what you were looking for...!

[DSguru2B]

I would go with Craig's suggestion, have two tables, one table with all vendor info and the second with all the orders with a fk to the vendor table.

[ray.wurlod]

Depending on your need, and whether there are any columns dependent on just one column of the primary key, you may be able to get away with just the one table, with one row for each order. You can select distinct vendor from this as required.