Preventing circular in DB

[mannygo]

I am trying to write a php/mysql that will allow a church keep attendance on their members in bible study. I am also going to try to prevent doing a circular reference between tables and just can't figure it out how since I am just starting to learn mysql. Here are the tables:

 

Table 1: Members:

---------------

1:Name:

2:Address:

3:Bible Study Group it belongs to:

 

Table 2: Cells

--------------

1:Bible Study Lider:

2:Bible Group Name

 

Table 3: Attendance

--------------

1:Date

2:Member

3:Bible Group

 

As you all can see, Table 2:2 makes a lookup at Table 1 for the member(in this case, the leader). BUT Table 1:3 makes a lookup to Table 2:2

and is a circular lookup.

 

Anyone have an idea on how to properly do this without any circular problems?

 

Thanks in advance!

 

 

 

 

 

[Barand]

Can a person belong to more than one group?

[mannygo]

Yes.

[Jacques1]

Then why does each member only have one group in your table layout?

 

Either way, once you have an extra table which assigns members to groups, then there's no longer a circular reference, and the problem goes away.

 

By the way, in your current layout, a person may be the leader of a group but not one of its members. Is that a problem?

[mannygo]

Here is the table update, I did not think when I first wrote it :

 

Table 1: Members:
---------------
1:Name:
2:Address:
3:List Bible Study Group(s) it belongs to:


Table 2: Groups
--------------
1:Bible Study Lider:
2:Bible Group Name
3:Members that belong to this group


Table 3: Attendance
--------------
1:Date
2:Member
3:Bible Group

 

And as I understand, there is only suppose to have a Parent reference and not both tables as Parents. I am kind of lost here on DB talk, I am still learning...

Can you put some samples?

[Barand]

Something like this

 

[Jacques1]

This is worse than the first attempt, mannygo.

 

There are no “lists” in a relational table. A single field contains a single value. If you want a many-to-many relation between two tables, you need a third table which connects the rows -- just like you did in your attendance table:

group_members
- group_id (references groups table)
- member_id (references members table)

Edited November 5, 2014 by Jacques1

[mannygo]

@Jacques1: I know, I am still learning all about php/mysql and it's hard to understand some of the concepts on programming.

 

I just need a way to be able to get FROM the members table, to which groups it belongs that particular user.

 

@Barand: Seems great logic but again, is there a way to have in the members table, a reference to which groups the member belongs to without resorting to call the group member table?

 

 

Thanks to both

[Jacques1]

No, there's no other way. That's how the relational model works. Either accept it, or don't use SQL. There are many alternative database models which you may like better (search for “NoSQL”).

Edited November 5, 2014 by Jacques1

[Psycho]

mannygo, I know you are just learning and that it seems like more work to create yet another table, but it really is the solution. Unfortunately, may, many people fall for the same "quick fix" idea that you are wanting. It may seem like it makes sense, but you are painting yourself into a corner. You will be back here days or weeks later wanting to do something that is very difficult or impossible with the solution you came up with.

 

With very, very rare instances, you should never store a list of values in a single field in the database. Although, one correction to Barand's provided structure. The group_id in the attendance table should be an INT.

[Barand]

 Although, one correction to Barand's provided structure. The group_id in the attendance table should be an INT.

 

Oops! Well spotted. Sorry about that. MySql Workbench defaults to varchar(45) and I forgot to change that one.