SQLite & repeated fields

[Riccardo Linzitto]

Hi Sarah,
In a relational schema you can't have multiple attributes (i.e. Fields with
multiple values). This is a feature of a ER database model.
The relational theory provides simple algorithms to obtain an equivalent
schema in 4NF.
One of these states that to model a multiple attribute you have to use
another relation (table) with the value of the multiple attribute and the
external key.
In your example you will have two relations:
Products with attributes:
    product id (primary key)
    product name
Items with attributes:
    item description
    quantity
    product id (external key)

This is a relational schema with minimum redundancy.

I hope this helps

Riccardo Linzitto

> Da: Sarah Reichelt <sarah.reichelt at gmail.com>
> Risposta: How to use Revolution <use-revolution at lists.runrev.com>
> Data: Thu, 8 Jan 2009 10:27:48 +1000
> A: How to use Revolution <use-revolution at lists.runrev.com>
> Oggetto: SQLite & repeated fields
> 
> Hi All,
> 
> I'm setting up an SQLite database and having some problems with table
> design. The app is inventory control with a "Make" function i.e. it
> stores the quantities of each raw material you have and when you
> "make" a specified amount of a certain product, those amounts are
> automatically deducted from the raw material stocks.
> 
> I have a very simple table for the raw materials, but the products
> table is not so obvious (at least not to me), mainly because each
> product will have a different number of items used in it's
> manufacture.
> 
> Do I have to set up a large table allowing for the maximum possible
> number of items?
> e.g.
> product name
> product id
> item 1
> quantity 1
> item 2
> quantity 2
> .
> .
> .
> item 50
> quantity 50
> 
> or is there any better way to hold a variable number of repeating fields?
> 
> TIA,
> Sarah
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution