Having a list of values in one table, what would be the clean way to go
to *design* a default value? (in an ER diagram)

I do NOT want to know about setting default values for CREATE TABLE
statements when adding data, the question is how to design this
information so that it can be read into my application.

example table:

----------

Thank you very much everybody, your help is enormously appreciated!

Michael

Values do not belong in an ERD. They belong in the data dictionary document
that is submitted with the rest of your assignment. What you are talking
about *is* an implementation detail, not a design detail. Depending on the
tool you are using, you may be able to build the list and select the default
automatically, but they still don't belong on the ERD.

An ERD shows abstract business relationships among the data- not
implementaion level items.


"Michael Fuchs" <fuze (AT) REMOVE (DOT) THIS.STUFFgmx.net> wrote

information so that it can be read into my application. <<

I'll take a guess at this:

CREATE TABLE Foobar
(...
currency_code CHAR(3) NOT NULL
REFERENCES Currencies(currency_code),
...);

CREATE TABLE Currencies
(currency_code CHAR(3) NOT NULL PRIMARY KEY,
priority_nbr INTEGER NOT CHECK (priority_nbr IN (1,0),
-- the following is SQL-92, but not in all products yet
CHECK ((SELECT SUM (priority_nbr) FROM Currencies) = 1),
...);

Then use a scalar subquery to find the #1 code when a NULL is given
instead of an explicit currency code to the input procedure:

INSERT INTO Foobar
VALUES (..., COALESCE (:explicit_curency_code,
(SELECT curency_code FROM Currencies WHERE
priority_nbr = 1)), ...);

Thanks a lot for your answer, but to be honest I do not quite
understand. I thought there is a way to somhow define a default value in
a db table. (like you can design hierarchies with unary relationships;
that was one possibility I was thinking of: to 'define' the topmost as
the default value)

Are you proposing to store this information outside the db in lets say a
plain text file and giving up basic integrity constraints?

The point is that I need to store this information on defaults in my db
(MS SQL Server 2k btw.).

Thanks a lot once again,

Michael


Alan wrote:

Your original question was about ERDs.


"Michael Fuchs" <fuze (AT) REMOVE (DOT) THIS.STUFFgmx.net> wrote