Re: Looking for comments - Mailing list pgsql-sql

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.Elphick@lfix.co.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@altern.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 ===========