Re: Looking for comments - Mailing list pgsql-sql

<snip>
1. General comment: you use lettercase to divide words in table
and fieldnames; this will be lost as soon as you create the tables,
because PostgreSQL folds everything to lower case.  Use _ 
to separate words instead.
</snip>

OK it should be done everywhere where I did some 
changes, at least.   

<snip>
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.
</snip>

OK I'll start searching for it but haven't implemented yet

<snip>
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.
</snip>

I think I solved it in a particular way.
I believe it keep things more understandable for me this way.
                 
<snip>                  
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 
</snip>

Solved ?                  

<snip>
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. 
</snip>

I took a very simple approache for now.
The idea is like access bits on Unix sytems.                                   
<snip>                  
If member ids cannot be negative, you need a CHECK 
constraint to check the id range.  The sequence will 
not override a direct setting.
</snip>

Yeap & nop 
Library member Id's are to me any thing that suites the Librarian

<snip>                  
You define person_ref twice; presumably the first 
occurrence should be `id'.
</snip>
Yeap                  


<snip>
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_fkeyREFERENCES 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. 
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)
</snip>

What do you tink of my usage of an intermediate table ?


<snip>
4.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 
<snip>

Simply because of multilinguisme.
The countries' name are not anymore a person's name, 
They have to be translated. (it could also be the case for towns but
only stupid Belgian keep double naming for their towns... who cares,
thus @ the moment 'bout that ).
I also look for a mean to use ResourceBundles so basic users can
introduce 
new countries via the modifications of simple text files.


<snip>
5.ZipCodes
I don't understand the purpose of this table. 
Presumably you need a PRIMARY KEY (country_code, zip_codes) 
constraint 
</snip>

Well because these are also redunddant & you could imagine 
that a Library has all the post-code for its resident in a 
table to avoid mistakes.






Site should be update soon.
At th emoment the site is still in Beta ...
mail is thus safer .

Thomas,