locked
database design options- better one RRS feed

  • Question

  • User-1202579601 posted
    StuFacultySubjId

    Stu_id

    Faculty_id Subj_id

    The three fields store fixed char type of data. So storing as a single field or as separate fields is better. I know to merge them while storing and separating them while

    extracting from the database based on their fixed size and position.

    Saturday, January 5, 2019 2:26 AM

All replies

  • User409696431 posted

    Storing them separately is better.  Why incur the overhead of merging them and splitting them every time you store or read them?  And I assume you will want to use those individual values to query other tables in the database, at some point.

    Saturday, January 5, 2019 10:26 AM
  • User-1202579601 posted

    Stu_id char(10), FacultyId char(10), subjId char(10). Nearly 5000*200*6*10 in order records are being stored. So mergerid char(30). For this particular table, which is the beter way

    in terms or performance.

    Saturday, January 5, 2019 11:55 AM
  • User475983607 posted

    Stu_id char(10), FacultyId char(10), subjId char(10). Nearly 5000*200*6*10 in order records are being stored. So mergerid char(30). For this particular table, which is the beter way

    in terms or performance.

    The approach decreases performance.  The fields should be separate not concatenated.

    Saturday, January 5, 2019 1:55 PM
  • User379720387 posted

    Why would you want to store foreign keys in char format?

    Those should be INT.

    You are building in unnecessary complexity, and 10 extra processing steps for the processor to convert char to binary.

    Sunday, January 6, 2019 4:46 PM
  • User-893317190 posted

    Hi pmdrait,

    It seems your Stu_id, Faculty_id and Subj_id are all foreign key fields, which means every fields refers to a id column of another table separately.

    How do you make them refer to another table when you combine them into a single field?

    For example , if you want to find the subjects of a student , you should join the table with student table and subject table, if you combine them together , it will be hard to join tables.

    If I misunderstand what you mean, please provide more information of what the fields means in you case and tables have relationship with the three fields .

    Best regards,

    Ackerly Xu

    Monday, January 7, 2019 2:48 AM