Database design on 1 to many approach

Question

Hi all,

I am learning database design, given with the following business requirement. Which design approach should I take?

1. Each customer can apply many applications.
2. Each company can apply many applications.
3. Each organisation can apply many application.

Each approach, I thought of has a draw back. If you were to choose which will be the correct design?

A) Approach 1

Application

Application_PK
Application_Date
Application_Detail
Customer_PK
Company_PK
Organisation_PK

B) Approach 2

Application

Application_PK
Application_Date
Application_Detail

ApplicationToCustomer

Application_PK
Customer_PK

ApplicationToCompany

Application_PK
Customer_PK

ApplicationToOrganisation

Application_PK
Organisation_PK

Answer 1

The foreign key in a one-to-many relationship usually goes in the "many" table, pointing back to the "one" table. So it should be "approach 2".

You don't say what the inverse relationships are. Can an application be owned by more than one customer? More than one organization? If yes, those are "many-to-many" relationships. In that case you have to add a join table between the two:

CUSTOMER -------- CUSTOMER_APPLICATION ------------ APPLICATION
(CUSTOMER_ID)     (CUSTOMER_ID, APPLICATION_ID)     (APPLICATION_ID)

Answer 2

EDIT:

-- All users of the application with ApplicationID = 7
select
      a.ApplicationID
    , u.UserId
    , u.UserType
from User_Application  as x
join Application       as a on a.ApplicationID = x.ApplicationID
join User              as u on u.UserId        = x.UserID
left join Customer     as c on c.UserID        = u.UserID
left join Company      as m on m.UserID        = u.UserID
left join Organization as r on r.UserID        = u.UserID
where  a.ApplicationID = 7 ;

Answer 3

The specs implicitly say that you have a many-to-many relationship (CMIIW). It's good to use such 'bridge table'.

Well, to choose which design is correct is easy. The second approach is correct. But it's harder to choose which design is better. It depends on your another 'implicit' specs. Performance.

I guess, your first approach is 'less normal'. I (again) guess it will perform faster than second approach which is 'more normal'. But this approach should not be used unless the second one is proved to be significantly slower.