What's the best practice in relationing 2 or more relationship tables?

I have a trip (primary: idTrip), where I can link more packages (primary: idPackage), so, I got a relationship table to link trips with packages (primary: idRelTripPackage). (relationship n-to-n)

And next I got a registrations table (primary: idRegistration). How do I best link those (1-to-1 relationship)?

1. I add two columns in the registrations table (idTrip, idPackage)?
2. I add a relationship table where i link idRegistration, idTrip, idPackage?
3. I add a relationship table where i link idRegistration, idRelTripPackage?
开发者_开发技巧

Am I right in thinking the relation from Registrations is to RelTripPackage, and its definitely one-to-one. There are a couple of options:

1: As it really is a one-to-one there's not really anything to stop you putting the Registrations data directly onto RelTripPackage, or doing the vice-versa and putting idPackage and idTrip straight onto Registrations as FKs, with a unique key across the two FK columns to ensure there aren't duplicates.

2: If do want the two separate tables then just add idRetTripPackage to Registrations as an FK, and then add a unique constraint on it - again to ensure uniqueness.

There's no need for a separate relationship table as its a 1-1 relationship - They only really become relevant when you are using an n-n. The rest of the time FKs should be placed directly on the child table.

If you follow that logic, you will

• add tables and Relations every time you need to add Relations
• end up with confusing or duplicate Relations (multiple paths between any two tables)

However the problem (limiting factor) is that the tables you are starting with are not actually normalised. Since the starting position does not have a good basis, you will end up with far more Relations (in tables) than there actually are between the Entities. So the best advice is, the Best practice is, before you attempt this current extension, step back and normalise the data, the existing tables. Then the extension will be much easier, and you will end up with less tables.

• if you provide info re the tables (Person, Trip, Package, etc); what exactly is a Registration, etc ... I can provide more explicit answers.

Generally any attribute that is 1::1 with the PK of an Entity should be an attribute in that entity. Any attribute that is 1::0-1 with the PK of an Entity should be in a separate table.

ER Diagram

Based on the information provided, this is your ▶Entity Relation Diagram◀. As long as you use Relational Identifiers, all the Relations you have identified thus far are supported directly (otherwise, if you use IDs, you will need more Relations and tables).

Readers who are unfamiliar with the Relational Database Modelling standard may find ▶IDEF1X Notation◀ useful.