locked
Table Structure RRS feed

  • Question

  • Hi all,

     I am new to database designing.... While creating master table I have to create a table for Bank. I assumed the columns details like Bank Name, BranchName, Country, State, City.

    I created structure as :

    Create table GV_BankDetails

    (

      BankName varchar(30),

      BranchName varchar(50),

      Country varchar(20),

      State varchar(20),

      City varchar(20)

    )

    But I think that if I create this table then there will be anamolies while updating as will not follow normalisation rules.

    So what changes should I make in this table structure?

    Please suggest.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh


    • Edited by Kapil.Kumawat Friday, March 15, 2013 9:51 AM
    • Moved by Naomi NEditor Friday, March 15, 2013 4:24 PM Better answer can be here
    Friday, March 15, 2013 9:51 AM

Answers

  • CREATE TABLE COUNTRY
    (
    	CountryID INT
    , CountryCode CHAR(10)
    , CountryName VARCHAR(100)
    )
    CREATE TABLE City
    (
    	  CityID INT
    	, CountryID INT
    	, CityCode CHAR(10)
    	, CityName VARCHAR(100)
    )
    
    CREATE TABLE Branch
    (
    	  BranchID INT 
    	, BranchName VARCHAR(50)
    	, ContactID INT
    )
    
    CREATE TABLE BranchContactInfo
    (
     	  ID INT 
    	, BranchID INT
    	, ContactNo VARCHAR(15)
    	, ContactPerson VARCHAR(15)
    	, POrtal VARCHAR(15)
    	---- Other Columns regarding Contacts
    )
    
    Create table GV_BankDetails
    (
    	ID INT,	
      BankName varchar(30),
      BranchID varchar(50),
      BranchContactID INT,
      CountryID INT,
      [State] varchar(20),
      CityID INT
    )

    I have figured out some tables, It totally depends upon your requirements, What information You need to save, 

    What you want on reports.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    • Marked as answer by Kapil.Kumawat Sunday, March 17, 2013 4:56 AM
    Friday, March 15, 2013 10:03 AM

All replies

  • I am not sure but try split bankdetails to bank_table and country_table. Meaning Bank details separate table and Region details separate table

    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you



    Friday, March 15, 2013 10:01 AM
  • CREATE TABLE COUNTRY
    (
    	CountryID INT
    , CountryCode CHAR(10)
    , CountryName VARCHAR(100)
    )
    CREATE TABLE City
    (
    	  CityID INT
    	, CountryID INT
    	, CityCode CHAR(10)
    	, CityName VARCHAR(100)
    )
    
    CREATE TABLE Branch
    (
    	  BranchID INT 
    	, BranchName VARCHAR(50)
    	, ContactID INT
    )
    
    CREATE TABLE BranchContactInfo
    (
     	  ID INT 
    	, BranchID INT
    	, ContactNo VARCHAR(15)
    	, ContactPerson VARCHAR(15)
    	, POrtal VARCHAR(15)
    	---- Other Columns regarding Contacts
    )
    
    Create table GV_BankDetails
    (
    	ID INT,	
      BankName varchar(30),
      BranchID varchar(50),
      BranchContactID INT,
      CountryID INT,
      [State] varchar(20),
      CityID INT
    )

    I have figured out some tables, It totally depends upon your requirements, What information You need to save, 

    What you want on reports.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    • Marked as answer by Kapil.Kumawat Sunday, March 17, 2013 4:56 AM
    Friday, March 15, 2013 10:03 AM
  • Hi Kapil,

    Do branches of banks not have IDs, I'm in the UK and each branch has something called a sort-code which is a 6 digit number displayed as xx-xx-xx. 

    The only other way I could suggest keep 1NF is by keeping them in seperate tables and creating a branch identifier yourself that could be used within a basic relational structure. Such as BranchID could be copied across to a regional or statewide table like rdineshkumar suggested.

    EDIT: Junaid's example is what I was thinking. Although there's no primary or foreign key relationships there.
    Friday, March 15, 2013 10:08 AM

  • EDIT: Junaid's example is what I was thinking. Although there's no primary or foreign key relationships there.

    @Kapil Plz create Primary Key and Foreign Key constraints by your self I think that is self explanatory what is Primary Key and Foreign Key(s) is table.

    Thanks Jonny for identifying. 


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    Friday, March 15, 2013 10:30 AM
  • CREATE TABLE Tblcountry
    (
     CountryID INT PRIMARY KEY NOT NULL IDENTITY(1,1),CountryCode CHAR(10), CountryName VARCHAR(100)
    )
    CREATE TABLE TblCity
    (
       CityID INT PRIMARY KEY NOT NULL IDENTITY(1,1)
     , CountryID INT FOREIGN KEY REFERENCES Tblcountry(CountryID )
     , CityCode CHAR(10)
     , CityName VARCHAR(100)
    )

    CREATE TABLE TblBranch
    (
       BranchID INT PRIMARY KEY NOT NULL IDENTITY(1,1)
     , BranchName VARCHAR(50)
     , ContactID INT
    )

    CREATE TABLE TblBranchContactInfo
    (
        ID INT PRIMARY KEY NOT NULL IDENTITY(1,1)
     , BranchID INT FOREIGN KEY REFERENCES TblBranch(BranchID  )
     , ContactNo VARCHAR(15)
     , ContactPerson VARCHAR(15)
     , POrtal VARCHAR(15)
     
    )

    Create table TblGV_BankDetails
    (
     ID INT, PRIMARY KEY NOT NULL IDENTITY(1,1)
      BankName varchar(30),
      BranchID varchar(50),
      BranchContactID INT FOREIGN KEY REFERENCES TblBranchContactInfo(ID ),
      CountryID INT FOREIGN KEY REFERENCES Tblcountry(CountryID ),
      [State] varchar(20),
      CityID INT FOREIGN KEY REFERENCES TblCity(CityID  )
    )

    Regards

    Ganesh

    • Proposed as answer by Sathishkmr Friday, March 15, 2013 4:06 PM
    Friday, March 15, 2013 12:18 PM
  • Nothing you have done is right. This is not a table; it has no key and cannot ever have a key. You live in a magical world where everything is VARCHAR(n). To get you started, we have an ISO country code that is three digits; Google it and write at least one column correctly. 

    You failed to do even the most basic research. Have you ever heard of the the ABA? What about IBAN? What industry standards did you research? NONE. 

    We will worry about normalization when you have shown us a table. We are nto her to do your homework for you. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, March 15, 2013 4:12 PM
  • Nothing you have done is right. This is not a table; it has no key and cannot ever have a key. You live in a magical world where everything is VARCHAR(n). To get you started, we have an ISO country code that is three digits; Google it and write at least one column correctly. 

    You failed to do even the most basic research. Have you ever heard of the the ABA? What about IBAN? What industry standards did you research? NONE. 

    We will worry about normalization when you have shown us a table. We are nto her to do your homework for you. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    AS first time I am working on Database design so I was confused regarding that table structure so I decided to take suggestion from you expert guys.... I am asking you to do my homework

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Sunday, March 17, 2013 4:56 AM