locked
Data Model RRS feed

  • Question

  • a question in data modeling please ,

    i have two tables ,

    1- customers(customer_id ,customer_name,  phone , email).

    2- addresses (address_id , address) ,

    the above tables generated a third table , which is

    - customers_addresses , because the relation between them is " many to many" , so the columns should be

    (customer_id , address_id) ,

    what is wrong with replacing the above two columns(customer_id , address_id)

    with(customer_name,address_name,phone,email) ,

    may be i should add (customer_id) as a pk , i do not know why , but i think i should ,

    then delete the two tables above (customers and addresses)

    what is wrong with that ?

    thanks a lot

    Thursday, August 7, 2014 7:06 AM

Answers

  • i am saying that i will delete the two master tables which are (customers and addresses) , and work only with the junction table to add customers and their addresses , what is wrong with that ? look above to the columns i mentioned again please .

    Ok in that case it should be fine

    Only thing is  all customer related attributes would be repeated for every related address in case of multiple addresses being present for a customer


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, August 8, 2014 3:50 AM
  • Hello ,

    I think you need to study an article "Ten Common Database Design ".
    Because firstly you need to know design concept and remove confutation then you will get your specific question's ans. 

    https://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Sunday, August 10, 2014 7:51 AM
  • >> Only thing is  all customer related attributes would be repeated for every related address in case of multiple addresses being present for a customer .

    if you mean the (phone , email) columns , it will not be repeated , it will be null . am i wrong ?

    Nope I meant any other customer attributes you may have like status,CustomerNo, etc

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, August 10, 2014 10:03 AM

All replies

  • You can do:

    with(customer_id, customer_name,address_name,phone,email) 

    single table design.

    It is a practical design. If there is significant data duplication then consider the 3-tables (including the junction table) design.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012











    • Edited by Kalman Toth Thursday, August 7, 2014 4:59 PM
    Thursday, August 7, 2014 8:28 AM
  • Your third junction table has  to be normalized(means to be in third normal form) But if you want to generate a query and output  the fields you want to, there is no problem generate a CTE as Kalman suggested. I might be wrong but customer_id + address_id should be declared as PK in the junction table.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, August 7, 2014 10:19 AM
  • Hello u cab use  public ICollection<class type>lass type; and then creat Mappers and repositry then you can creat your owen Library and make aconnection to other app

    Thursday, August 7, 2014 10:27 AM
  • a question in data modeling please ,

    i have two tables ,

    1- customers(customer_id ,customer_name,  phone , email).

    2- addresses (address_id , address) ,

    the above tables generated a third table , which is

    - customers_addresses , because the relation between them is " many to many" , so the columns should be

    (customer_id , address_id) ,

    what is wrong with replacing the above two columns(customer_id , address_id)

    with(customer_name,address_name,phone,email) ,

    may be i should add (customer_id) as a pk , i do not know why , but i think i should ,

    then delete the two tables above (customers and addresses)

    what is wrong with that ?

    thanks a lot

    Replacing ids with names means you're adding data redundancy ie same data will exist in master as well as junction table. Any modification in customer/address names means you've to reflect it in junction table as well ie update anomalies.

    In comparison if you place reference ids in junction table, it will remain the same irrespective of any modifications happening to name or any customer/address attributes in the customer and address table so you dont have to worry on how to get changed applied to junction table as well . Also since ID is still the same it will still be able to link to appropriate records in customer/address tables to extract associated customer and address related information.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, August 7, 2014 10:46 AM
  • i am saying that i will delete the two master tables which are (customers and addresses) , and work only with the junction table to add customers and their addresses , what is wrong with that ? look above to the columns i mentioned again please .
    Thursday, August 7, 2014 4:56 PM
  • i am saying that i will delete the two master tables which are (customers and addresses) , and work only with the junction table to add customers and their addresses , what is wrong with that ? look above to the columns i mentioned again please .

    Hi eslam,

    According to your description, as other posts, you can delete the customers table and addresses table, and just work with a junction table. It seems that there is nothing wrong, if you get any errors, please post the detailed error message for analysis.  

    In addition, because the relation between customers table and addresses table is " many to many", you need to add customer_id and address_id columns as a composite PRIMARY KEY  in the junction table, which will be convenient to manage your data. Below is the syntax of creating customers_addresses table, do you want to display the result as follows?

    USE <databasename>
    GO
    
    CREATE TABLE customers_addresses
    (customer_id varchar(10) not null,
     address_id  varchar(10) not null,
     customer_name varchar(50),
     address_name varchar(50),
     phone varchar(50),
     email varchar(100)
    )
    
    ALTER TABLE customers_addresses 
       ADD CONSTRAINT PK_CUSTOMER_ADDRESS PRIMARY KEY (customer_id, address_id);
    
    INSERT INTO customers_addresses
    VALUES('10001','001','Crystal','Street5',40090087,'crystal@hotmail.com'),
                 ('10001','003','Crystal','Street1',40096087,'crystal@hotmail.com'),
                 ('10002','001','Jane','Street5',40090027,'jane@hotmail.com'),
                 ('10002','002','Jane','Street2',40090067,'jane@hotmail.com')
    
    SELECT *FROM customers_addresses
    
    --Below is the result



    Thanks,
    Lydia Zhang


    Friday, August 8, 2014 3:38 AM
  • i am saying that i will delete the two master tables which are (customers and addresses) , and work only with the junction table to add customers and their addresses , what is wrong with that ? look above to the columns i mentioned again please .

    Ok in that case it should be fine

    Only thing is  all customer related attributes would be repeated for every related address in case of multiple addresses being present for a customer


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, August 8, 2014 3:50 AM
  • >> Only thing is  all customer related attributes would be repeated for every related address in case of multiple addresses being present for a customer .

    if you mean the (phone , email) columns , it will not be repeated , it will be null . am i wrong ?

    Friday, August 8, 2014 5:00 AM
  • Hello ,

    I think you need to study an article "Ten Common Database Design ".
    Because firstly you need to know design concept and remove confutation then you will get your specific question's ans. 

    https://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Sunday, August 10, 2014 7:51 AM
  • >> Only thing is  all customer related attributes would be repeated for every related address in case of multiple addresses being present for a customer .

    if you mean the (phone , email) columns , it will not be repeated , it will be null . am i wrong ?

    Nope I meant any other customer attributes you may have like status,CustomerNo, etc

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, August 10, 2014 10:03 AM
  • It is better to let EF manage the primary keys for you. Thus, the auto generated id index is a reliable way to do so instead of using keys like "customer_name". 

    If I am not wrong, entity keys cannot be edited in code, thus, if you use "customer_name", and you are developing an app to access this, you will never be able to modify a key using code.

    The only reason i would think of adding attributes to the Join table, "customer_address" is if you want to add more information to the table, such as "logging information" or "description".

    Remember, the Join table is just used for the relationship, otherwise, all your data is stored in the many to many tables, i.e., "customer" and "address".

    Finally, i am not sure i would use a many-to-many relationship for customer and customer_address. I would possibly use, one-to-one, or if a customer has multiple addresses, then, i would use one-to-many.



    There is never infinite resources.

    • Proposed as answer by KCWamuti Monday, August 11, 2014 5:26 AM
    Monday, August 11, 2014 5:25 AM
  • so helpful ,

    thank you all ,

    i am happy to know that there is a forum like this from which i can get help in db design .

    wait for my questions ,

    thank you

    Monday, August 11, 2014 6:22 AM