Group association in DB or not?

Databases
1

Hi everyone,

I am struggling with figuring out long term how to deal with “groups” in my software. I had been thinking of grouping as a literal view thus keeping it out of my database tables. Now that I am designing additional features I have now begun to rethink this. How do you do it in this context?

Listbox Control

FLOCK GROUP
---- CHICKEN A
---- CHICKEN B

Etc.

All group’s can be deleted, new groups can be added, persons can be reassigned to different groups.

With this example do you still treat the groups as a view or do you add the group id to a column in the person’s SQL table (have a group table with relation)? or another way? :slight_smile:

Thanks for your suggestions in advance.

Mike

2

I would add a “groupID” to a related table, so there is no “question” as to what group it belongs to
if “Chicken A” has a record ID, then I would create a group table to allow any Chicken to belong to multiple groups if required

chickenID
chickenNAME
groupID
chickenID
groupID
groupDESCRIPTION
3

Thanks Dave!

4

If I were making an offering to our database goddess…

CREATE TABLE t_Chicken (
  Chicken_id INTEGER PRIMARY KEY AUTOINCREMENT,
  ChickenName TEXT,
  OtherSimpleDetail INTEGER);

CREATE TABLE t_Flock (
  Flock_id INTEGER PRIMARY KEY AUTOINCREMENT,
  FlockName TEXT,
  OtherSimpleDetail BOOLEAN);

CREATE TABLE tx_FlockAssociation (
  FlockAssociation_id INTEGER PRIMARY KEY AUTOINCREMENT,
  Chicken_id INTEGER REFERENCES t_Chicken(Chicken_id),
  Flock_id INTEGER REFERENCES t_Flock(Flock_id);

You could use this model to load chickens by flock association, flocks by chicken association, all flocks with X number of chickens and so on. This model allows one chicken to associate themselves with multiple flocks if you’ve got an international chicken. You can also programmatically limit the flock association.

With an ORM like ActiveRecord the data organization becomes the code organization. I find that really helpful for personally understanding the data, which helps me communicate it to the user.

5

Thanks guys - Yeah in prep for what I thought would be recommended I have this coding in progress:

(Redo-ing now based on Tim/Dave’s posts)

Tim/Dave - thank you for the multiple association idea!

6

I tend to do what @Tim Parnell does. tend is the key word as sometimes the method what @Dave S suggested is better.

in this case I would do Tim’s method. especially if flocks can come/go, and chickens can be moved from flock to flock on the fly.

7

Indeed they will be — Thanks Scott!

8

Am I on the right track? Specifically how the foreign key reference is positioned? (My ORM gen code matches, but I figured i’d still quality it :slight_smile: )

Thank you again so much - I am very glad I asked the question :slight_smile:

9

that is pretty much how I’d do it

10

@Mike Cotrone yes. I would make the flockAssocTable be unique across a combo of chickenID and flockID. you dont want multiple entries in the table for the same association. it is not necessary but will help keep the data clean.

12

dont make each of the fields unique but unique combo. as you will have more than one chick in each flock. but yeah.

13

definitely what scott said as if you make flock id unique you cant add more than 1 chicken to a flock

you want a unique on (chickenID, flockID )

14

Thanks Beaker! :slight_smile:

15

Thanks again everyone!

16

I think they mean make a unique constraint on your flockAssocTable. The constraint would be a unique compound of chicken_id and flock_id.

17

Ill add that later when i get into debugging – Thanks Tanner

18

BTW, what app is it that you’re using for modeling? Looks very clean.

19

SQL Editor:

https://www.malcolmhardie.com/sqleditor/

It works very well for my needs and it the UI is clean like you suggested :slight_smile:

20

I don’t see the need for a many-many relationship between flock & chicken. However you might want to add a datejoined column to the flockassoctable so you can track chicken movements between flocks. This might be important if a flock needs to be terminated due to disease etc.

21

Curious… is this app to really track CHICKENS?? or was that just a place holder to make the question easier to ask?