Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Posts
    13

    Question Database Design Issues

    Hi,

    I am really confused with the structure that I like to create. I like to create phonebook for teachers and students.

    For students I structured like this

    DROP TABLE IF EXISTS `jaidev2_mce`.`st_phone_book`;
    CREATE TABLE `jaidev2_mce`.`st_phone_book` (
    `stphonebook_id` int(10) unsigned NOT NULL auto_increment,
    `reg_id` int(10) unsigned NOT NULL,
    `teltype` varchar(45) NOT NULL,
    `telno` varchar(45) NOT NULL,
    PRIMARY KEY (`stphonebook_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    This is gud if I have only students as reg_id is student's id.

    But what If I like to add teachers phonebook in this structure too.

    Should I create new phonebook table for teachers as I have too add teacherid in existing table which I think will violate the rules of db.

    Thanks in advance.

    Jay Khatri

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The starting question with this sort of thing is why you think it necessary to store phone numbers in a separate table in the first place? I'm not saying it is wrong, just that it is rarely necessary.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2010
    Posts
    13

    Exclamation Database Design Issues

    by this way people can add multiple phone no. if some have one it will be just one and someone have five he still can enter. and even normalization rule itself says like that. Means we have to remove the frequency of columns.

    There are many situations where we found that two tables can denote to single primary key. I would like to have perfect solution for such stage.

    Thanks & Best Regards

    Jay Khatri

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You have three choices then.
    Add a teacher id column to the table. Create a constraint that one or both id columns must be populated (only though allow both to be populated if teachers and students can share phones).
    Create two phone tables.
    Use a sub-type super-type model for students and teachers and relate your phone book to the person entity.

    Or just do what most people do and have multiple phone columns for people. Although repeating groups are usually to be avoided most designers don't worry about it when it comes to phones.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •