Nov 25, 2019

Level 5

Suggestion for many to many relationship, and data repentance laravel

Guys i have a big doubt in my project.

still now i worked with one to many relation only in my projects. iam not too familiar with many to many relationship.

iam working with a school project. got too confused with fee module. need a suggestion

i need to frame the fee structure of the school.

this is my table strucutre : https://imgur.com/KxLpQWI

I have drawn the table in my note: https://imgur.com/rmJWDSK

i have drawn the unstructured table also: https://imgur.com/DD1CHud

the requirement is, each class posses different fee structure and each fee structure has sub categories say for example,

Class: V std 

Term1 (main category)

        Tution fee  -   5000
        Van Fee     -   2000
        Extra Curicular-    4000
        Total           11000

Class: X std 

Term1 (main category)

        Tution fee  -   2000
        Van Fee     -   2000
        Extra Curicular-    4000
        Total           8000

In the above example for class V std term1 fee is 11000 with its sub categories and for class X std term1 fee is 8000 with its sub categories.

So already i have a table ClassDetails with columns

id      class_name      note
1       I std           this is 1st std
2       IIstd           this is 2nd standard
.
.
.so on

Now for fee structure how to frame the table? i know i need to have two tables feeMainCategory and FeeSubCategory.

but i need to link class with Feemainctegory and feemaincategory with feesubcategory

For this how many intermediate tables i need to have? how to frame a table. just i made the tables without intermediate. working fine, but the data are repeated.

my feemaincateogy table must possess the following columns

id  year        class_id        feemainName     note    status  amt
1   2019            1           Term1           ..      active  6000
2   2020            2           Term1           ..      active  9000

my feesubcateogy table must possess the following columns

id  feemaincat_id       subcatname      amt     note        status  
1   1                   busfee          3000        ...     active
2   1                   extrafee            3000        ...     active
3   2                   bus             8000        ...     active
4   2                   tutionfee       1000        ...     active

How i can mingle this tables. in this status is important because based on year those details changes. say for example, in 2019 term1 is active. but in 2021 they will stop term1 and have a different name with different amount. so i need that tooo. even sometimes the same term1 will for next year the amount will be increased. how to update this all?? kindly suugest me with ideas please. got so confused with relationship of many. please suggest with example table or any other links also please? can i have a extra column in my intermediate table?? like this status??

i have already asked this question many times. but still confused. i have shorten the question in this thread. if doubts you can refer the below threads for this question please.

Refer:

https://laracasts.com/discuss/channels/laravel/problem-any-to-many-relationship-with-class-and-fees

https://laracasts.com/discuss/channels/guides/problem-in-fetching-records-from-db-need-suggestion-for-columns-in-table

jeffreyvanrossum

6 years ago

Level 9

I am not entirely sure if I get what your trying to achieve. But in general, a many-to-many relation is established by the use of a so called pivot-table.

For example, let's say you are dealing with posts and post categories. A post may have multiple categories, so we need a pivot table which for could look something like this:

Table: posts
- id (primary key)
- name
- date

Table: categories
- id (primary key)
- name

Table (pivot): category_post
- post_id (foreign key reference to id on posts)
- category_id (foreign key reference to id on categories)

Maybe you already knew this, in that case, feel free to ignore my post ;-) If not, I hope it might give you some insight that gets you closure to a solution for your specific problem.

bugsysha

6 years ago

Level 61

You've got me all confused. If you have fees then just create fees table and put everything there. If you need to have fees table work with multiple objects then use polymorphic relationship. That way it will be easier to fetch all data from one table than to have to work with multiple tables.

AbdulBazith

6 years ago

Level 5

@jeffreyvanrossum @bugsysha thank you guys thank you for your response.

Ya i know that post and tags where we can use pivot table.

ok let me ask a simple doubt?

i have two tables,

ClassDetails with columns

id      class_Name
1       V std
2       VI std

SubjectDetails with columns

id      subcode     subname
1       S001        English
2       S002        Maths
3       S004        Science

Now a single class can have many subjects and a single subject corresponds to many class.

for this iam doing a pivot table, ClassDetails_SubjectDetails

id      class_id        subject_id
1       1           1
2       1           2
3       2           1

here is my doubt i need a column status= active or inactive for both subject and class, because, i have a column acc_year in all tables. this year there may be english subject. but for next academic year they may remove the subject. i cant delete it, because old sudent marks are there. so i will inactive it, so for next year it wont come, so where i should use this status?? in class table or subject table or pivot table??

after using all these if i need to retrieve the subjects which belongs to a specific class the how i can do that???

Kindly suggest your ideas

bugsysha

6 years ago

Level 61

If you want to control subject per class then in pivot table. If you want to control subject for all classes then in subject table.

Please or to participate in this conversation.