SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    staff table-booking app

    I am making a booking app. There is one table that holds the bookings.
    A second table holds details for the (business)users for WHOM the bookings are made. Details such as name etc.

    I want to make also a 3rd table that will hold staff names, staff of the business users.

    So, the bookings table must ALSO hold the staff name the end user chose (such as the case of a hair salon).
    The problem is how to connect all these tables, the relationship between them.

    This is the scheme I have concluded.
    The staff table should have 3 cols:
    • The name of the staff


    • A business users col indicating with whom business user the specific staff member is related to (a foreign key here points to a business users table)


    • A staffID column which will serve as the primary key and at the same time a foreign key from the bookings table will point to it(the staff table) so as to indicate that booking with whom staff is related to.


    And to be precise here the SQL for the table;
    Code:
    CREATE TABLE `staff` (
      `name` varchar(45) NOT NULL,
      `staff_b_user` int(11) NOT NULL,
      `staff_ID` int(11) NOT NULL,
      PRIMARY KEY (`staff_ID`),
      KEY `fk_staff_business_users1_idx` (`staff_b_user`),
      CONSTRAINT `fk_staff_business_users1` FOREIGN KEY (`staff_b_user`) REFERENCES `business_users` (`crID`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    What do you think?

  2. #2
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Noobody????

  3. #3
    SitePoint Enthusiast GhostGambler's Avatar
    Join Date
    Apr 2007
    Location
    Germany, NRW
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You most likely do not want "NO ACTION" on UPDATE or DELETE. The minimum you want is "RESTRICT" (which would be default anyway, if you had not specified NO ACTION…) Otherwise your data might become inconsistent very fast. (Maybe one of the other options is also valid for your case ... take a look at the manual.)

    Rest looks good. I personally don't like your column naming, but this is just personal preference.


Bookmarks

Posting Permissions

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