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.
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.
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.
Chris
On Fri, 5 Sep 2008, Andreas Ntaflos wrote:
> On Friday 05 September 2008 22:41:14 Igor Tandetnik wrote:
>> Andreas Ntaflos
>>
>> <[EMAIL PROTECTED]> wrote:
>>> On Friday 05 September 2008 21:57:30 Igor Tandetnik wrote:
>>>> Andreas Ntaflos
>>>>
>>>>> Naturally a room cannot be identified without a building so it is a
>>>>> weak entity (this seems to be the canonical example in all database
>>>>> books I've seen).
>>>>>
>>>>> I'd like to auto-increment the ID numbers in both tables but as far
>>>>> as I can see the AUTOINCREMENT keyword only works the way it is used
>>>>> in the Building table. That is, only "INTEGER PRIMARY KEY
>>>>> AUTOINCREMENT" is syntactically correct.
>>>>
>>>> Wait: if you want roomID to be autoincrement, then it's going to be
>>>> unique (which, after all, is the point of autoincrement). Run it by
>>>> my again: why can't you identify a room without a building?
>>>
>>> Well a room's existence depends on the building in which it is.
>>> Different buildings can use the same room numbering scheme so in
>>> order to know which room we are looking at we need to know its owning
>>> building. Hence a room is a weak entity.
>>
>> If you expect rooms in different buildings to have the same ID, then you
>> can't use autoincrement for it anyway.
>
> I understand now what you mean. Somehow I failed to realise that
> auto-incrementing (whether using a SERIAL or AUTOINCREMENT) increments an
> attribute in the table regardless of what it is of which the table is a
> representation. There would not be any two rooms with the same room number,
> regardless of the building they are in.
>
>>> But it seems I misunderstood the point of AUTOINCREMENT. I am looking
>>> for something like PostgreSQL's SERIAL data type [1] so when creating
>>> new rooms I don't have to manually specify the roomID. Instead the
>>> next possible roomID should be chosen automatically when INSERTing.
>>
>> But then you won't have two rooms with the same ID, and there won't be
>> any problem making roomID a primary key.
>>
>> I don't see how you plan to have it both ways.
>
> It doesn't make much sense, yes. I am sorry for the confusion.
>
> My problem statement remains partially valid, though. Auto-incrementing the
> roomID in the Room table would make each room have its very own room number
> but the primary key still would need to consist of both buildingID and roomID
> or else the modelling of a room as a weak entity wouldn't work correctly. The
> buildingID is still a foreign key so SELECTing all rooms of one particular
> building is still possible but that's only part of the conceptual model.
>
> I suppose it's not something the authors of books on database systems would
> approve of. A weak entity's primary key has to include the primary key of its
> owner entity or else it isn't weak. Yet in the case of SQLite and
> AUTOINCREMENT this does not seem possible.
>
> Anyway, thanks for opening my eyes to the obvious :)
>
> Andreas
> --
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
