Jump to content

Preventing circular in DB


mannygo

Recommended Posts

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!

 

 

 

 

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by Jacques1
Link to comment
Share on other sites

@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

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.