CODEWITHSUNDEEP
database-design
ilovetolearn
ilovetolearn

Reputation: 2060

Database design on 1 to many approach

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

Upvotes: 0

Views: 117

Answers (3)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

alt text

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 ;

Upvotes: 2

Hendra Jaya
Hendra Jaya

Reputation: 1628

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.

Upvotes: 0

duffymo
duffymo

Reputation: 308763

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)

Upvotes: 2

Related Questions