Re: New arrivals (II)

On 27/06/2007 20:26, Richard Robinson wrote:

I need to represent a number of items which each contain a (variable) number
of key/value pairs (and some other stuff, but never mind that). The names of
the keys are not known in advance, and the values for each key can be
multiple; and what I need to get back is a list of items, selected according
to various wotnottery and sorted on the (possibly multiple) values of one of
the keys, ie multiply-valued items appearing in each of the possible
positions. Which last is the bit that becomes amusing.

I've kind of assumes you're using M$ access, so I've convertified from ORACLE/Postgres in my head, not checked for sillies.

so you want an items table
I'll assume you are going to assign the id values, rather than let the database generate them
primary key (i.e unique index) on item.id
indexes on anyotherstuff that's appropriate

you don't need your extra /two/ tables, but can collapse them into one keyval table
let the database automagically set the id or set a DEFAULT value from a SEQUENCE
primary key (i.e. unique index) on keyval.id
probably want an index on (keyval.item_id)
or on (keyval.item_id + keyval.key)
you'll survive without one, but speed might suffer.

=======================================

CREATE TABLE item (
id INTEGER,
colour TEXT,
weight FLOAT,
PRIMARY KEY (id));

CREATE TABLE keyval (
id COUNTER,
item_id INTEGER,
key TEXT,
value TEXT,
PRIMARY KEY(id));

INSERT INTO item(id, colour, weight)
VALUES(1001, "blue", 19.3);

INSERT INTO keyval(item_id, key, value)
VALUES (1001, "blue_alpha", "aaaa");

INSERT INTO keyval(item_id, key, value)
VALUES (1001, "blue_beta", "bbbb");

INSERT INTO item(id, colour, weight)
VALUES(999, "green", 24.5);

INSERT INTO keyval(item_id, key, value)
VALUES (999, "green_gamma", "gggg");

INSERT INTO keyval(item_id, key, value)
VALUES (999, "green_delta", "dddd");

INSERT INTO keyval(item_id, key, value)
VALUES (999, "green_epsilon", "eeee");

INSERT INTO keyval(item_id, key, value)
VALUES (999, "green_epsilon", "another_eeee");

then you can do queries like

SELECT item.id, item.colour, item.weight,
keyval.key, keyval.value
FROM item, keyval
WHERE item.id = 999
AND keyval.item_id = item.id
ORDER BY keyval.key;

For bonus marks you can force referential integrity of the item_id to the item.id

ALTER TABLE keyval
ADD CONSTRAINT FK_keyval_item
FOREIGN KEY (item_id) REFERENCES
items (id);
.

Relevant Pages

  • Re: formula for linear regression
    ... > posts in this thread how familiar you are with regression. ... you might have data on the weight and height of 50 ... > The TREND function will take account of the numeric relationship between ... This is termed "multiple regression." ...
    (microsoft.public.excel.worksheet.functions)
  • Re: many to many problem
    ... DivisionID completes the compound primary key and acts as ... a single division of a single company -or- multiple divisions of a single ... > l beginning with Starting Out and Database Design 101. ... tblLinkCompanyContacts is a ManyToMany linking table Can I create ...
    (microsoft.public.access.tablesdbdesign)
  • Re: many to many problem: John Vinson and Lynn Trapp - help?
    ... DivisionID completes the compound primary key and acts as ... > a single division of a single company -or- multiple divisions of a single ... >> l beginning with Starting Out and Database Design 101. ... > tblLinkCompanyContacts is a ManyToMany linking table Can I create ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Composite unique key with some fields that are null
    ... If you have an AutoNumber for primary key, ... You can make a composite index on the fields that contain nulls, but I don't think making it a unique index is a good idea. ... If you do need to create a unique index where some fields are known to have no value, you can use a zero-length string (zls) instead of a Null. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Are Primary Keys Necessary?
    ... I have a table called with CompanyID as ... MemoDescription, but no primary key. ... to keep your database performing at its best you would want an index on ... The only difference between a PK and a unique index is that a unique index can ...
    (microsoft.public.access.tablesdbdesign)
Loading