Page 1 of 1
Help with a new table design
Posted: Mon Apr 27, 2009 11:57 pm
by 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
Re: Help with a new table design
Posted: Tue Apr 28, 2009 12:28 am
by Pagadrai
yaminids wrote:
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.
Posted: Tue Apr 28, 2009 1:36 am
by ray.wurlod
Use a database that can handle multi-valued fields, such as UniVerse, Unidata, jBase, D3, etc.
Posted: Tue Apr 28, 2009 6:54 am
by 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?
Re: Help with a new table design
Posted: Tue Apr 28, 2009 8:38 am
by vivekgadwal
yaminids wrote:
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...!
Posted: Tue Apr 28, 2009 8:58 am
by 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.
Posted: Tue Apr 28, 2009 4:17 pm
by 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.