Geek Answers Handbook

Designing tables: from one to many and one to one at the same time?

I'm not sure if I'm modeling this type of relationship correctly, maybe someone could figure out if that really sounds:

Suppose we have a typical parent-type relationship in which each parent can have many children, but we need to keep track of the parent's lonely (sigh) beloved child ...

Table Parent

- FavoriteChildID

and

Table child

- ParentID

therefore, the links intersect, where one is one to one and the other is one to one. Is this considered a good design? Would it be better to have a column in the child table where only one row can be marked as favorite at any time? Something else?

Thanks.

+1
source share
2 answers

How about an extra table?

table favourite( ,parent_id ,child_id ,primary key(parent_id) );

This will help if for all your favorite queries.

+1
source

The best design, in my opinion, is one that will prevent data inconsistency and prevent people from having enough rope to hang themselves.

If you need only one favorite for the parent, perhaps the parent should have a favoriteChildId column.

If you put a column for your favorite flag in a child, you will need to do more work so that two children of the same parent cannot be marked as favorites.

If you suspect that in the future you may have more than one favorite, then, of course, the extra work may be worth it.

Design really depends on the relationship and whether there may be changes in the future to the possibility of different "favorite" relationships.

+1
source

More articles:


All Articles