Can I suggest another way that I adopted a long time ago to solve this
kind of issue?
Forget the autoincrement function included in your RDBM and use a
customized one more or less similar to the one I developped in my ZQLib
(adapted from ZEOSDBO) for Delphi (previously I did the same thing with
MS-Access):
{==Primary key management============
Primary Key is a shaped string which consists of prefix+serial number
PkSize is the width of the serial number
Const NullString='';
M_ZQLite is a module hosting various ZEOSDBO objects, especially ZQread
which is a TZReadOnlyQuery
PrimKeyZero, PrimKeyMax and LastPrimKey may be private.
}
Function PrimKeyZero(PkSize:integer):string; //lowest serial number value
var i:integer;
begin
Result:=NullString;
For i:=1 to PkSize Do Result:=Result+'0';
end;
Function PrimKeyMax(PkSize:integer):string; //highest serial number value
var i:integer;
begin
Result:=NullString;
For i:=1 to PkSize Do Result:=Result+'9';
end;
Function LastPrimKey(Table, PkField,Prefix: String;var
PkVal:string):boolean;
begin
Result:=False;
With M_ZQLite Do
BEGIN
ZQread.SQL.Clear;
ZQread.SQL.Add('SELECT MAX('+PkField +') FROM '+ Table +
' WHERE '+ PkField + ' LIKE '+QuotedStr(Prefix+'%')+';');
Try
ZQread.Open;
if ZQread.IsEmpty
Then
PkVal:=NullString
else PkVal:=ZQread.Fields[0].AsString;
Result:=True;
Finally
ZQread.Close;
End;
END;
End;
Function NextPrimKey(Table, PkField,Prefix: String;PkSize:integer;var
PkNext:string):boolean;
var
LastK: String;
N:longint;
code:integer;
Begin
Result:=False;
If not LastPrimKey(Table, PkField, Prefix,LastK) Then exit;
If LastK=NullString
Then Begin
PkNext:=Prefix + PrimKeyZero(PkSize);
Result:=True;
Exit;
End
Else LastK := Copy(LastK,Length(Prefix)+1, PkSize);
If LastK =PrimKeyMax(PkSize)
Then begin
ShowMessage(Glob_PrimKeyOver);
exit;
End;
Val(LastK,N,code);
PkNext:= Prefix + Format('%*.*d',[PkSize,PkSize,N+1]);
Result:=True;
End;
Assuming that the Civil Work DB has to manage les than100 buildings,
each one containing less than 10000 rooms.
When adding a building:
if NextPrimKey(T_Building, 'BuildingID',NullString,2,NextBuilding)
then ZQrun('INSERT INTO T_Building(BuildingID,.....)
VALUES('+QuotedStr(NextBuilding)+',;;;;;);');
When adding a room:
if NextPrimKey(T_Room, 'RoomID',CurrentBuilding,4,NextRoom)
then ZQrun('INSERT INTO T_Room(RoomID,.....)
VALUES('+QuotedStr(NextRoom)+',;;;;;);');
I would point out that this approach permits meaningfull keys which
offer a lot of possibilities.
Igor Tandetnik a écrit :
> "Andreas Ntaflos"
> <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>
>> On Friday 05 September 2008 23:59:25 Igor Tandetnik wrote:
>>
>>> Define "weak entity". Precisely what properties does it have that are
>>> not represented in the model?
>>>
>> A weak entity has no possible combination of attributes that would
>> uniquely identify it in the relational model. It's existence depends
>> on another entity. The building-room-example illustrates this nicely.
>> There's nothing to hinder two separate, uniquely identifiable
>> buildings to have the same room number scheme, so to identify a
>> single room the key of the building is needed in combination with the
>> room's numer.
>>
>
> Well, in this case you've violated your "conceptual model" the moment
> you decided to assign unique auto-generated numbers to your rooms, in a
> way completely divorced from how numbers would be assigned to real rooms
> in real buildings. As the room is no longer a "weak entity" by your
> definition, of course you have difficulty modeling it as one. Why again
> are you surprised?
>
>
>> The property the desired model has that are not represented in this
>> model we discuss is that a room's primary key does not contain a
>> reference to the primary key of the owning entity. It is globally and
>> uniquely identifiable without ever looking at the building.
>>
>
> And that is bad because...?
>
>
>> This is a practical consequence and thus the model you propose is
>> possible and probably even feasible, but it is no longer the model I
>> wanted to implement. Plus I don't really see why it would be wrong to
>> use an auto-incrementing key for the roomID while keeping the tuple
>> (roomID, buildingID) as a primary key.
>>
>
> What's the point? What's so special about PRIMARY KEY that you
> absolutely must have one? All it does is guarantee uniqueness - but you
> already have that. You are getting hung up on the syntax of your CREATE
> TABLE statement, rather than on the properties of the underlying
> relation.
>
> Let's put it this way: are there any queries that you can't run against
> the current model, but would be able to run against your hypothetical
> ideal model? And if there are no such queries, what's the difference
> between the two?
>
>
>> It seems that there are two choices available: go with your model,
>> which is simpler and certainly more efficient, but loses an aspect of
>> the desired model (namely that rooms are by themselves not uniquely
>> identifiable).
>>
>
> Again, it's not the model that loses the property - it's your initial
> decision of using unique room numbers. After that, you just insist on a
> syntactical game of make-believe, for no other reason that to make
> yourself feel better, as far as I can tell. You say: I want to assign
> unique numbers to rooms, but I want to pretend the rooms are not
> uniquely identifiable by those unique numbers. Does this really make
> sense to you?
>
> Igor Tandetnik
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
