CODEWITHSUNDEEP
sqldata-modeling
solarissf
solarissf

Reputation: 1277

SQL tables design layout

I'm trying to design my database with very basic tables and I am confused on the CORRECT way to do it.

I've attached a picture of the main info, and I'm not quite sure how to link them. Meaning what should be a foreign key, or should some of these tables include of LIST<> of the other tables.

layout

UPDATE TO TABLES updated tables

Upvotes: 0

Views: 266

Answers (2)

Muthaiah PL
Muthaiah PL

Reputation: 1158

As per your requirements, You are right about the associative table

  1. Client can have multiple accounts And Accounts can have multiple clients
    Then, Many (Client) to Many (Account)
    So, Create an associate table to break the many to many relationship first. Then join it that way
  2. Account can have only one Manager
    Which means One(Manager) to Many(Accounts)
    So, add an attribute called ManagerID in Accounts
  3. Account can have many traedetail
    Which means One(Accounts) to Many(TradeDetails)
    So, add an attribute called AccountID in TradeDetails

Upvotes: 2

David Cram
David Cram

Reputation: 778

Depends on whether you are looking to have a normalized database or some other type of design paradigm. I recommend doing some reading on the concepts of database normalization and referential integrity.

What I would do is make tables that have a 1 to 1 relationship such as account/manager into a single table (unless you can think of a really good reason not to). Add Clientid as a foreign key to Account. Add AccountID as a foreign key to TradeDetail. You are basically setting up everything as 1 to many relationships where the table that has 1 record for the id has the field as a primary key and the table that has many has it as a foreign key.

Upvotes: 0

Related Questions