Re: Looking for comments

Oliver,
At the moment Tx for all your remarks...
I'll need some times to digest them all!
I'll keep this thread inform on updates

Regards,

Thomas

========================
Oliver Elphick wrote:
>
> Thomas SMETS wrote:
> >
> >Rather thant making long sentences & comment.
> >Anyone willing to give me a little help on this tables definition is
> >welcome
> >
> >http://lautre.org/tsmets/DB.html
>
> General comment: you use lettercase to divide words in table and field
> names; this will be lost as soon as you create the tables, because
> PostgreSQL folds everything to lower case. Use _ to separate words
> instead.
>
> book:
> ISBN's have a checkdigit; it would be sensible to provide a function
> to be used in a CHECK constraint to ensure that the ISBN is valid.
> NOT NULL and UNIQUE are implied by PRIMARY KEY; they don't need to be
> specified.
>
> What information goes in reference?
>
> You create indexes yourself. Indexes on these fields are automatically
> created because of the PRIMARY KEY and UNIQUE constraints; your own
> indexes add nothing and will decrease performance. On the other hand,
> you could well use indexes on author and publisher. Perhaps you
> could also do a word index on title.
>
> What if you have more than 1 copy of the same title? You should have
> another table for physical copies, with a foreign key reference to book.
> When you lend a book, it is the physical copy you want to track.
> The copy table will cross-reference the member who has it on loan
> and will also need a field for status (on the shelf, on loan, lost/stolen,
> rebinding, etc.) The price belongs here, because you might pay a different
> price for a later acquisition of the same ISBN. You will then need
> yet another cross-referencing table between book and copy tables.
>
> Of course, some titles have multiple ISBNs, depending on the type of
> binding (e.g. Good News Bible in several different formats). Perhaps
> you need yet another table to link ISBNs to titles. Each issue of
> many serials has a volume and issue number; you really don't want a
> separate definition in book for each issue, do you?
>
> Author: many books have multiple authors; also editors.
>
> You probably need fields for place and year of publication
>
> Type: this seems to refer to attributes of serial publications; these
> have ISSN numbers (rather than ISBN) and the ramifications of checking
> serial issues are far more complex than you have allowed for. Serials
> certainly need a separate table.
>
> member:
> You create a sequence yourself and use it explicitly for person_ref; it is
> simpler to define this field as SERIAL instead of INTEGER; this will
> do all the sequence maintenance for you.
>
> If member ids cannot be negative, you need a CHECK constraint to check
> the id range. The sequence will not override a direct setting.
>
> You define person_ref twice; presumably the first occurrence should be `id'.
>
> You say that one member can reference multiple persons, but you cannot
> achieve that by referencing a single person in this table. A single
> field can hold only a single reference. You need a member_person table:
>
> CREATE TABLE member_person (
> member INTEGER CONSTRAINT member_fkey REFERENCES member (id)
> ON UPDATE CASCADE ON DELETE NO ACTION,
> person INTEGER UNIQUE
> CONSTRAINT person_fkey REFERENCES person (id)
> ON UPDATE CASCADE ON DELETE NO ACTION,
> PRIMARY KEY (member, person)
> );
>
> which will hold all persons related to the member. If you have a person
> who is primarily responsible, his id goes in the person_ref field.
>
> I should have thought that person_address should have a NOT NULL constraint.
>
> Why make LastLending NOT NULL? If you have a new member there is no
> last lending and the field would naturally be null.
>
> The CHECK constraint on CreatedOn is invalid; a date field cannot ever
> have a value of '' (it is not held as a string). The NOT NULL constraint
> is all you need; though you could add a date range check
> (CreatedOn > '1 Jan 2001' and CreatedOn <= CURRENT_DATE)
>
> CountryCodes:
> Why not add a name field and preload this table with the ISO country
> definitions. (Some of the country codes are not at all obvious, so
> you need the names.) I expect the Post Office would prefer to have
> names, too.
>
> The PRIMARY KEY constraint makes UNIQUE NOT NULL unnecessary. There is
> no sense in having a DEFAULT on a primary key field. The default belongs
> in the address table.
>
> ZipCodes:
> I don't understand the purpose of this table.
> Presumably you need a PRIMARY KEY (country_code, zip_codes) constraint.
>
> Translations:
> ditto
>
> You are defining an unnecessary index.
> Your insert will violate the NOT NULL constraint on language.
>
> My general impression is that you're making this up as you go along.
> You could do with finding a book on librarianship; there are an awful
> lot of details to consider in a good library application.
>
> --
> Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
> Isle of Wight http://www.lfix.co.uk/oliver
> PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
> ========================================
> "But I say unto you, That every idle word that men
> shall speak, they shall give account thereof in the
> day of judgment." Matthew 12:36

--
Sat Dec 30 00:39:50 CET 2000

Thomas SMETS e-mail : tsmets(at)altern(dot)org
Av. de la Brabançonne 133 / 3 Tel. : +32 (0)2 742. 05. 94.
1030 Bruxelles
======= Quote of the Day =========
Ye've also got to remember that ... respectable people do the most
astonishin'
things to preserve their respectability. Thank God I'm not respectable.
-- Ruthven Campbell Todd
========= End of Quote ===========