Hi guys,

Is repeating groups inside repeating groups possible in normalisation... example:
UNF:
ORDER
OrderID
OrderDate
{ItemName
ItemDescription
ItemQuantity
ItemPrice
ParcelID
ParcelSendDate
ParcelItemsSent }

-------------------

ORDER_ITEM-1
ItemNo
OrderNo
ItemName
ItemDescription
ItemQuantity
ItemPrice
{ParcelID
ParcelSendDate
ParcelItemsSent }

- If the qauntity was 1000.... then the order would need to be split, thus creating a repeating group inside a repeating group....

So this normalised to 1NF would produce:

1NF:
ORDER
OrderID
OrderDate

ORDER_ITEM-1
ItemNo
OrderNo
ItemName
ItemDescription
ItemQuantity
ItemPrice

PARCEL-1
ItemNo
OrderNo
ParcelID
ParcelSendDate
ParcelItemsSent

It would make sense that normalisation would allow this.... but i just wanted to make sure... as in all examples i have found they deal with very small amounts of data and so never really cover "unique" situations

thx for reading :-)

--Philkills

I so rarely think in terms of 1nf....xnf

if the order can be sent in more than one dispatch/parcel then you have to have a mechanism (entity) which indentitifies that, so another entitiy linking the order ID to the parcel ID becomes relevant.

However its an academic exercise, so to a certain extent you can ignore the xNF game by applying rules and logic.. You could say that the customer NEVEr ships in more than one delivery

BTW how do you propose to deal with the connumdrum of a single despatch contianing items from more than one order. Before you go into sketching out the NF tree, consider ruling it out or in based on business logic. you may consider that yet another entity us relevant identifying what order(s) comprise what parcel(s), which were despatched / packed on waht day, using what delivery mechanism (post, courier, delivery co, hand deliver, own transport etc.......)

When you refer to the entity "Despatch" are you refering to sending out say 100 parcels on 1 Truck to a depot...etc?

If so i don't think i need to go into that kind of complexity ;p

but was that the correct way to handle repeating groups inside repeating groups.. or is there a better way? :-)

btw thx for the help ^_^

--Philkills

I'd simply handle the "repeating groups" using foreign keys. In other words, I'd have one table for orders, another table for parcels, then a third table to track the relationship of parcels to orders. That way an order shipping as a single parcel is easy, one row in each table. An order shipping in many parcels is easy too, one row for the order, one row for each parcel, and one row to establish the relationship between them.

The case that happens all the time in the real world is when you have many orders, many parcels, and many combinations... A customer has three orders that ship in five parcels or vice versa. By using the three table approach, you don't get your knickers in a twist no matter how things are ordered and/or shipped... It is just record keeping at that point.

-PatP