Thread: Looking for comments
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 Tomorrow the pageswill be colorized a little bit more & a drawing of the expected tables will be provided. Tx, Thomas, -- Thu Dec 28 23:51:18 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 ========= The story of the butterfly:"I was in Bogota and waiting for a lady friend. I was in love, a long time ago. I waited three days. I was hungry but could not go out for food, lest she come and I not be there to greet her. Then, on the third day, I heard a knock.""I hurried along the old passage and there, in the sunlight, there was nothing.""Just," Vance Joy said, "a butterfly, flying away." -- Peter Carey, BLISS ========= End of Quote ===========
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 ===========
<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,
Thomas SMETS wrote: > > <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> Here's a URL with more information:http://www.isbn.spk-berlin.de/html/userman/usm4.htm. I've written a similar algorithm in C to create and verify checksums for eight digit codes. The final code is then 9 digits. I did this to help create codes that are the same length as a social security number. If you'd like a copy, let me know. -Ron-
Thomas SMETS writes: > <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 In the PostgreSQL distribution there's a directory contrib/isbn_issn that defines a couple of data types that might help you. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/