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?
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
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.
@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.
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.