Hi All,

I have a table and each row is a company - the name of the company,
the website, etc. What I would like is one of the entries to be a list
of products that the company sells - and here's the important part - I
want the list of items to be set up so that each item can have its own
description in another table.

If I just make the list of products a comma separated list, that won't
link to anything properly.

Do I need to do something like create a table for every company, so
each table can then contain a list of items? If I did that, how would
I link each table to the "products entry" of the corresponding
company?

There's probably a better way to do this than anything I've mentioned,
so any suggestions are welcome.

(I'm using openoffice base 2.2)


-rick

Rick Giuly <rgiuly.group (AT) yahoo (DOT) com> wrote:

Create a second table with a key of company and item.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Rick Giuly <rgiuly.group (AT) yahoo (DOT) com> wrote:
Google for "normalizing" a database.

Are you familiar with the concept of related tables? Foreign keys?

The rows in a table must depend on the key that identifies the row, the whole
key, and nothing but the key. (First, second and third normal forms,
respectively, for the googler in you.)

A list of "company products" within the company table violates that; you would
need a key column to further identify the "product". (Google for "repeating
groups" in a database model.) Therefore you need a separate "companyproducts"
table. That other table will have the company key column(s), repeated in
order to allow a "JOIN" between the two tables. The join column(s) represent
the relation between the two tables - the rows in the "child" table that have
a certain (set of) value(s) in common with a "parent" table row are the child
rows that relate to the corresponding parent row. A child row will also have
an additional column or columns to further identify which product, belonging
to that company, is described in the row.

Example (Please view the data types as chosen for example purposes):
CREATE TABLE companies
(
company VARCHAR (120) PRIMARY KEY,
info CLOB
);
CREATE TABLE companyproducts
(
company VARCHAR (120) NOT NULL,
product VARCHAR (120) NOT NULL,
info CLOB,
PRIMARY KEY ( company, product ),
FOREIGN KEY ( company ) REFERENCES companies ( company )
);

There are still problems with this approach. What if two different companies
carry the same product? As you might see in that case, the "info" column does
not depend on "the whole key" but only part of it, the "product" column. That
tells you that you need another table, for "products" independently of what
company has them. "companyproducts" is a relation that shows which "company"
rows relate to which "products" rows.

Example:
CREATE TABLE companies
(
company VARCHAR (120) PRIMARY KEY,
info CLOB
);
CREATE TABLE companyproducts
(
company VARCHAR (120) NOT NULL,
product VARCHAR (120) NOT NULL,
PRIMARY KEY ( company, product ),
FOREIGN KEY ( company ) REFERENCES companies ( company ),
FOREIGN KEY ( product ) REFERENCES products ( product )
);
CREATE TABLE products
(
product VARCHAR (120) PRIMARY KEY,
info CLOB,
);

Now you can query for all the products for a certain company using a three-way
table JOIN.

Example:
SELECT x.company, c.info AS company_info,
x.product, p.info AS product_info
FROM company c
INNER JOIN companyproducts x ON c.company = x.company
INNER JOIN products p ON x.product = p.product
WHERE x.company = ?
ORDER BY x.product;

This is a canonical example.

--
Lew

"Lew" <lew (AT) lewscanon (DOT) nospam> wrote

Just to be pedantic, it does not violate that (if by "that" you mean first
normal form). A list is a perfectly acceptable data type in principle. It
is just that if you elect to have an attribute which is a list the DBMS is
obliged only to treat as a kind of black-box, with no discernable internal
structure--just as it would if an attribute were a JPEG or an MP3 or any
other more complex type.

An attribute which is a list would probably be a nuisance, and most SQL
implementations don't support it, but relational theory has nothing to say
against lists as values or types.

Roy

Roy Hann wrote:
Yes, that is pedantic. Your point is valid but tangential to my explanation,
which intentionally used simple column types to avoid distraction. I was not
hoping to reveal the entire body of relational theory in a brief outline of
normalization intended to aid the OP's googling. Where I indicated "CLOB" as
an example type, one could substitute a list type (or INTEGER or whatever); it
wouldn't affect the message.

I have never seen an introductory discourse on normalization that endorsed or
even mentioned what you're saying. The example I presented is extremely
similar to the "guns-and-butter" examples in most introductions to the topic.
Presumably those writers adhered to the pedagogical theory that it is best
to avoid too many exceptions and curlicues early in the training process.

OTOH, your early mention of these curlicues is useful to let folks know at
least two things up front - that what I presented is far, far from the final
word on the matter, and not to be too dogmatic about rules of thumb gleaned at
introductory study levels.

--
Lew

"Lew" <lew (AT) lewscanon (DOT) nospam> wrote

Amen to that brother. That was my entire and only point.

Roy

On Jun 21, 3:03 am, "Roy Hann" <specia... (AT) processed (DOT) almost.meat>
wrote:
To be more accurate, only those DBMS products that attempt to
implement the relational model have this constraint. Many people work
with DBMS tools that can work with lists (I am working with
InterSystems Cache' and its MultiValue implementation right now). The
OP is thinking the way a human being who has not been, uh, tainted(?)
by "relational theory" would think, the same way those who model data
for NF2 (Non-First Normal Form, by the former meaning of 1NF)
databases are able to think, where a list of foreign keys is fine.

And how silly is that? I prefer to use a DBMS that is more list-savvy
than that.

For most SQL-DBMS's today, I agree.

Once upon a time it did and still, today, those practicing relational
theory avoid such constructs. But the good news is that relational
theorists are now saying that "relational theory has nothing to say
against lists as values or types." Progress. Perhaps someday our
database practices can return to where lists are obviously good
choices for a data modeler. That will require DBMS products and query
languages that work more fluidly with lists. --dawn

On Jun 20, 10:19 pm, Lew <l... (AT) lewscanon (DOT) nospam> wrote:

I think I get the basic idea:
Create a table with bunch of pairs (company, product) that will keep
track of a company having various products or a product having various
companies. And, use the pairs (company, product) as a key values in
that table - since each pair is guaranteed to be unique.

Although, I've just decided to buy an introductory text on the subject
because the answer was way more complicated than I thought it would
be. I haven't yet learned the syntax for SELECT, so it looks greek to
me.

Thanks very much to everybody.

-Rick