lookup tables

Hello,
I was wondering what the best way is to use lookup tables. i don't want to
use a new table for every lookup.
Basically what i want is a generic lookup table for all kinds of data. In
queries, to translate the integer value of the lookup, it should know which
description it should use in the lookup table.
Anyone knows a solution?This is the wrong approach. There are no "lookups" in a relational
database. A table models a single Entity - (all things of a similar
kind). Foreign keys are the mechanism used to guarantee referential
integrity between tables but your "universal lookup table" (all things
of any kind) destorys referential integrity because it makes these
integrity constraints impossible, or at least very difficult, to
implement.
The "lookup table" is a common error that comes from an
application-centric rather than data-centric approach to database
design. A wiser approach is to model the data first, then focus on
application presentation. One day other applications may want to access
the same data so it pays to have a correct and consistent data model.
In a normalized relational design a "lookup table" just wouldn't exist.
David Portas
SQL Server MVP
--|||HI David,
Thanks for the explaination. But what if the data in the "lookup" tables has
no relation. For example i have relational table where insert records about
pharmaceutical products. One of the fields contains a category value which i
"lookup" in the table lookupvalues.
Is this wrong?
Do you have an example how it should be?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111392935.643692.162300@.z14g2000cwz.googlegroups.com...
> This is the wrong approach. There are no "lookups" in a relational
> database. A table models a single Entity - (all things of a similar
> kind). Foreign keys are the mechanism used to guarantee referential
> integrity between tables but your "universal lookup table" (all things
> of any kind) destorys referential integrity because it makes these
> integrity constraints impossible, or at least very difficult, to
> implement.
> The "lookup table" is a common error that comes from an
> application-centric rather than data-centric approach to database
> design. A wiser approach is to model the data first, then focus on
> application presentation. One day other applications may want to access
> the same data so it pays to have a correct and consistent data model.
> In a normalized relational design a "lookup table" just wouldn't exist.
> --
> David Portas
> SQL Server MVP
> --
>|||I'm not sure what you mean by "no relation". "Lookup" is normally used
by programmers to refer to the parent table in a relationship. I assume
product categories might look like the example below. Note the foreign
key constraint and other keys.
Of course there is no requirement for every table to contain a foreign
key or be referenced by a foreign key. There are other types of
semantic relationships that aren't represented by constraints. However,
a table that was totally unrelated to another in the database by ANY
means probably wouldn't be particularly useful in queries and I don't
think anyone would call such a table a "lookup".
CREATE TABLE Product_Categories (product_category_code INTEGER PRIMARY
KEY, product_category_desc VARCHAR(50) NOT NULL UNIQUE)
CREATE TABLE Products (sku INTEGER PRIMARY KEY, product_desc
VARCHAR(50) NOT NULL UNIQUE, product_category_code INTEGER NOT NULL
REFERENCES Product_Categories (product_category_code) /* ... */)
David Portas
SQL Server MVP
--|||Hi David,
I understand your example, but in that way in need to create such a table
for every different category i have for different products. Below you can
find my example what i'm doing, please tell me if this the right way in
terms of good datamodelling. It's purpose is to define categories for
different types of data.
CREATE TABLE [Listcategories] (
[lcat_Id] [int] IDENTITY (1, 1) NOT NULL ,
[lcat_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Listcategories] PRIMARY KEY CLUSTERED
(
[lcat_Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Lists] (
[list_Id] [int] IDENTITY (1, 1) NOT NULL ,
[lcat_Id] [int] NOT NULL ,
[list_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Lists] PRIMARY KEY CLUSTERED
(
[list_Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Lists_Listcategories] FOREIGN KEY
(
[lcat_Id]
) REFERENCES [Listcategories] (
[lcat_Id]
) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
CREATE TABLE [Listoptions] (
[lopt_Id] [int] IDENTITY (1, 1) NOT NULL ,
[list_Id] [int] NOT NULL ,
[lopt_Value] [int] NOT NULL ,
[lopt_Label] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Listoptions] PRIMARY KEY CLUSTERED
(
[lopt_Id]
) ON [PRIMARY] ,
CONSTRAINT [IX_Listoptions] UNIQUE NONCLUSTERED
(
[list_Id],
[lopt_Value]
) ON [PRIMARY] ,
CONSTRAINT [FK_Listoptions_Lists] FOREIGN KEY
(
[list_Id]
) REFERENCES [Lists] (
[list_Id]
) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
Thnx
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111400843.222937.306360@.g14g2000cwa.googlegroups.com...
> I'm not sure what you mean by "no relation". "Lookup" is normally used
> by programmers to refer to the parent table in a relationship. I assume
> product categories might look like the example below. Note the foreign
> key constraint and other keys.
> Of course there is no requirement for every table to contain a foreign
> key or be referenced by a foreign key. There are other types of
> semantic relationships that aren't represented by constraints. However,
> a table that was totally unrelated to another in the database by ANY
> means probably wouldn't be particularly useful in queries and I don't
> think anyone would call such a table a "lookup".
> CREATE TABLE Product_Categories (product_category_code INTEGER PRIMARY
> KEY, product_category_desc VARCHAR(50) NOT NULL UNIQUE)
> CREATE TABLE Products (sku INTEGER PRIMARY KEY, product_desc
> VARCHAR(50) NOT NULL UNIQUE, product_category_code INTEGER NOT NULL
> REFERENCES Product_Categories (product_category_code) /* ... */)
> --
> David Portas
> SQL Server MVP
> --
>|||Going by the names alone, this doesn't look like a business data model.
It looks like a content management system implemented in SQL. Are you
trying to model pharmaceutical products or data entry screens? Is a
"List" really a businesss entity or just a menu on a combo box? If you
modeled the real meaningful data instead of the application metadata
then I don't think you would need stuff like "Options" and "Labels" in
the database at all - that data would appear as attributes in other
tables.
These questions are relevant because to build a Normalized data model
requires an understanding of the real-world scenario you are trying to
model and the relationships within it. Without that understanding I can
only give general advice. The most obvious problem is that all of your
tables have an IDENTITY key but no alternate keys. IDENTITY should
never be the only key of a table.
David Portas
SQL Server MVP
--|||Hi david,
It's for data entry screens. Is it done like what i did or should it be
approach differently.
All other tables are normalized.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111405645.646381.264350@.f14g2000cwb.googlegroups.com...
> Going by the names alone, this doesn't look like a business data model.
> It looks like a content management system implemented in SQL. Are you
> trying to model pharmaceutical products or data entry screens? Is a
> "List" really a businesss entity or just a menu on a combo box? If you
> modeled the real meaningful data instead of the application metadata
> then I don't think you would need stuff like "Options" and "Labels" in
> the database at all - that data would appear as attributes in other
> tables.
> These questions are relevant because to build a Normalized data model
> requires an understanding of the real-world scenario you are trying to
> model and the relationships within it. Without that understanding I can
> only give general advice. The most obvious problem is that all of your
> tables have an IDENTITY key but no alternate keys. IDENTITY should
> never be the only key of a table.
> --
> David Portas
> SQL Server MVP
> --
>|||On Mon, 21 Mar 2005 13:43:07 +0100, Jason wrote:

>It's for data entry screens. Is it done like what i did or should it be
>approach differently.
Hi Jaso,
No, it's not done like that.
You don't create tables for entry screens. In fact, you should reverse
the entire process: design tables first (starting from the business
requirements and normalizing to at least third normal form). Then, you
design data entry screens for these tables.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
Another question, when do i save the id or the value as text in a table?
Example, in a table of orders you can specify the employer which created the
order. In that case do you store the employeeid or the name of the employee.
Is there any rule when you need to store text instead of a int value?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:c1qu31l8aoab86upce73e95dajo0jg63oa@.
4ax.com...
> On Mon, 21 Mar 2005 13:43:07 +0100, Jason wrote:
>
> Hi Jaso,
> No, it's not done like that.
> You don't create tables for entry screens. In fact, you should reverse
> the entire process: design tables first (starting from the business
> requirements and normalizing to at least third normal form). Then, you
> design data entry screens for these tables.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 22 Mar 2005 21:01:35 +0100, Jason wrote:

>Hi Hugo,
>Another question, when do i save the id or the value as text in a table?
>Example, in a table of orders you can specify the employer which created th
e
>order. In that case do you store the employeeid or the name of the employee
.
>Is there any rule when you need to store text instead of a int value?
Hi Jason,
That's another deciusion that should be made in the design phase, as
part of the data model.
You'll first have to identify the appropriate key for each entity. For
employees, SSN is often used (but it can only be used if you're sure
that the SSN will be known for each employee), or the company might have
it's own EmployeeNumber or EmployeeCode. The number or code might be
numerical, but it needs not - I've also seen schems that use things such
as initials and a sequence number. For employees, the name is not a good
choice for primary key, as you'd run into problems if a second person
with the same name is employed.
When you start to create the tables to support the logical data model,
you can consider using a surrogate key. You should only do that if the
natural key (as identified in the logical data model) spans too many
columnsor is too long. For the various possible keys for employees, this
doesn't apply, so you don't need a surrogate key and you can simply
declare a PRIMARY KEY constraint on the column that holds the natural
key.
But suppose that projects are only identified in the company by the
combination of project titel (max 250 characters) and starting year,
you'd have a good reason to introduce a surrogate key ProjectId, make
that the primary key for the table and create a UNIQUE constraint to
ensure uniqueness of the columns that make up the natural key. The
surrogate key is often (but not necessarily always) an integer.
Now back to your question: how to store the data. If you add a new
employee who's just been hired, you'd have to store all data in the
appropriate format: name as character data, DOB as datetime, salary as
numeric(8,2) and so on.
If you only need to create a link to an existing employee (e.g. because
an employee has been appointed to work on a project), you use the
datatype corresponding to the primary key of the Employees table. If the
primary key is on SSN and SSN is declared CHAR(9), you'd use CHAR(9). If
the primary key is on EmpNumber and EmpNumber is declared smallint,
you'd use smallint. Etc.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)