On Saturday 06 September 2008 00:11:01 [EMAIL PROTECTED] wrote:
> I suspect somes confusion is at work here because the books you refer to
> which discuss this issue are likely referring to what is sometimes called
> 'natural keys' versus a key based on AUTOINCREMENTed numbers which have no
> direct correspondence to the object. Some entities have the property of
> serving as the own key (a 'natural' key), e.g, phone numbers. A table of
> phone numbers does not need a separate key from the phone number itself.
> However, room numbers (actual room numbers as seen and used in the real
> world) are not like this, hence the books describe room numbers as a
> 'weak' entity, where knowing the actual room number by itself is
> insufficient to specify the room in a multi-building database.
That's exactly what I was taught (or drilled in) in Data Modelling and
Database Systems 101 :)
> In the database schema, you can, as discussed in this thread, use an
> AUTOINCREMENT or SERIAL field in the rooms table to provide a unique ID
> number for each room. If your purpose is just to have a primary key in the
> database table, you are done. But if you want to store actual room numbers
> from real buildings, you need to store those numbers in a separate,
> non-unique field. Then you decide whether to use room+building as the
> 'natural' primary key, or a separate ('unnatural') AUTOINCREMENT value as
> a key.
This is what I failed to realise in the beginning. Thank you, too, for
clearing it up :) I want to keep the concept of rooms as weak entities intact
and need a way in SQLite to do it while still having the benefit of using an
auto-increment function. I believe Nicolas described a way of doing that [1].
> Thus, the confusion arises from the lack of distinction between a
> 'natural' primary key consisting of real room numbers and real building
> numbers, versus an internal primary key consisting of a serialized (but
> inherently meaningless) number. The database will do whatever you want,
> you need to decide which schema fits your needs.
I must confess I never really thought about primary keys any other way
than "natural". I am no DBMS designer; instead I rely on the DBMS to shield
me from as many internals as possible :) But then I have never before worked
with SQLite. One apparently works a bit closer to the internals here than in
PostgreSQL.
Anyway, thanks for clearing up the confusion a bit :)
Andreas
[1]
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2008-September/005650.html
--
Andreas Ntaflos
Vienna, Austria
GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
