Hi

I have a Family table:
CREATE TABLE "Family"
(
"FamilyID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"FamGroup" INTEGER,
"ClientID" INTEGER NOT NULL,
"FamPos" INTEGER
)

The Famgroup field is the glue to group all the members of a particular family. The idea is that ClientID 101 may have a relative in ClientID 234, and I need to 'group' them with a common FamGroup field value - i.e. 26. The question is - how do I ensure that we are given a new unique integer for the FamGroup field?

It isn't imperative that the FamGroup numbers are contiguous, but for maintenance purposes, that would be preferable. I would prefer integers to a GUID field.

Should I use a single field table to store the FamGroup numbers, or is there another way to store the numbers?

Regards & TIA

Peter

Peter


>The Famgroup field is the glue to group all the members of a particular family. The idea is that ClientID 101 may have a relative in ClientID 234, and I need to 'group' them with a common FamGroup field value - i.e. 26. The question is - how do I ensure that we are given a new unique integer for the FamGroup field?
>
>It isn't imperative that the FamGroup numbers are contiguous, but for maintenance purposes, that would be preferable. I would prefer integers to a GUID field.
>
>Should I use a single field table to store the FamGroup numbers, or is there another way to store the numbers?

I'd have a two column table - column1 the number and column2 a description. Make column1 an autoinc

Whilst there are lots of funky ways to store a number I'm guessing that you want to be able to pick one for each ClientID and you want to hold a list rather than just the last number used.


Roy Lambert [Team Elevate]

The usual way would be as Roy says to create a "FamGroups" table with an ID field (AutoInc) and perhaps a second field such as "name".

Each person in your first table would then have a "FamGroup" which could be picked from the FamGroups table.

Note that in your situation I would start with more "natural" data objects.

i.e. People, Families.

People:
ID, FamiliesID, Name, Description

Families
ID, Name, Description

Then all "People" would have a "Family" in a simple "parent-child" relationship.

All people with the same FamiliesID would be in a particular family.