Multiple Tables Need One to Many Relationship

multiple tables need one to many relationship

Create a comment table with a comment_id primary key and the various attributes of a comment.

Additionally, create A_comment thus:

CREATE TABLE A_comment (
    comment_id PRIMARY KEY REFERENCES comment(comment_id),
    A_id REFERENCES A(A_id)
)

Do likewise for B, C and D. This ensures referential integrity between comment and all the other tables, which you can't do if you store the ids to A, B, C and D directly in comment.

Declaring A_comment.comment_id as the primary key ensures that a comment can only belong to one entry in A. It doesn't prevent a comment from belonging to an entry in A and an entry in B, but there's only so much you can achieve with foreign keys; this would require database-level constraints, which no database I know of supports.

This design also doesn't prevent orphaned comments, but I can't think of any way to prevent this in SQL, except, of course, to do the very thing you wanted to avoid: create multiple comment tables.

one to many relationship between 3 or more table

We have no idea bout your real requirement and the architecture. So, this is just a suggestion only to keep relation between image table.Your attached image little bit confused.

1. You can keep hotel and Restaurant both in one table by using a category [ flag the record as Restaurant or Hotel] . lets call this table as "Organizations"
   Then you can keep your image table without any issue as in below table.

2. If you need to keep separate tables for Hotels and Restaurants as you explained, then you have to add a Type column [img_type] to your Image table to categorize the image. Based on the category selected, you have to insert "hotel id" or "restaurant id" in to the "img_referance" field.

Note: Some hotels they have their own restaurants. If you need to manage those "Restaurant" then you have to introduce "Type column" to your "Restaurant" table as well. In that case, create a new field as "hotel id" in the restaurant table. (one hotel can have many restaurants).

SQL - Best practice for One-to-Many with Multiple One Tables

Postgres has a solution for you, with table inheritance. It is well described in the documentation.

Basically, you can have an events table and then have three different types of events. You can have a foreign key reference to either the specific events or to the parent events table.

For instance, you can have a generic eventAttendees table. This would have a foreign key to events. But the reference could be to any of the specific event types. The specific event types would then have the common columns from event (perhaps only eventId) and only the columns for that specific type.

More than two tables with many to many relationship

You can create a table i think for storing relations. Where you can use all 3 IDs as foreign Key's.

Relation Table.

RELPKID    TeacherFKID   BookFKID  StudentFKID

1           1            2   

I hope this helps thank you

Multiple tables with one to many relationship(Laravel)

You should be using polymorphic relationships for this. It allows multiple models to make use of a single table.

Have a look at the documentation here

In your particular case, each of your tables would reference a noteable_id column and a noteable_type.

The noteable_id will contain the id of the (A/B/C) model.

The noteable_type will contain the string name of the model (A/B/C).

The (A/B/C) models will now get a new attribute:

/**
 * (A/B/C) Model(s)
 */
public function notes()
{
    return $this->morphMany('App\Notes', 'noteable');
}

And the note model will initiate it's polymorphic properties against the attribute name used to identify your polymorphic ids and types:

/**
 * Note Model
 */
public function noteable()
{
    return $this->morphTo();
}

Now you can simply call ->noteable on the (A/B/C) models, and they all share 1 table without the need of another pivot table for each table.

---

Related Topics