Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder MrBiggZ's Avatar
    Join Date
    Apr 2005
    Location
    Indianapolis IN
    Posts
    345
    Thanks
    44
    Thanked 2 Times in 2 Posts

    Question about primary keys

    Hi!

    I think I may have made a slight miscalculation when I started this whole database. Being it's my first and not a really big database should be easily adjustable.

    I have my teams table that is like this:

    Question about primary keys-capture.jpg

    Now I created the team-id which is some random string. I can use that as my primary key correct? I can get rid of what I have there now the ukey field with is a primary key and is auto incremented. I don't reference that anyplace else. I do reference team-id in all my other tables and that is what I would use to create one-to-one and one-to-many queries.

    Below is a sample of what in the teams table.

    Question about primary keys-capture1.jpg

    I think this many make my life a bit easier but I'm 100% on it. I guess after I do this like 50 more times I'll become more comfortable with it!

    Thanks much in advance!

    Dave
    “No matter how slick the demo is in rehearsal, when you do it in front of a live audience, the probability of a flawless presentation is inversely proportional to the number of people watching, raised to the power of the amount of money involved.” ~ Mark Gibbs

  • #2
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,944
    Thanks
    2
    Thanked 170 Times in 165 Posts
    What DB GUI tool are you using?

    I'm not sure if it matters to others here, but I would find it much better/cleaner if you could post the mysql cli output showing your table structure.
    For example:
    Code:
    mysql> show create table devices\G
    *************************** 1. row ***************************
           Table: devices
    Create Table: CREATE TABLE `devices` (
      `IP` int(10) unsigned NOT NULL,
      `MAC` bigint(12) unsigned NOT NULL,
      `division` varchar(24) DEFAULT NULL,
      `network` int(10) unsigned NOT NULL,
      `hostname` varchar(24) NOT NULL,
      `department` tinyint(2) unsigned DEFAULT NULL,
      `type` varchar(3) DEFAULT NULL,
      `switch` varchar(16) DEFAULT NULL,
      `port` tinyint(2) DEFAULT NULL,
      `authorized` tinyint(1) NOT NULL,
      `comments` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`MAC`),
      KEY `type` (`type`),
      KEY `IP` (`IP`),
      KEY `department` (`department`),
      CONSTRAINT `devices_ibfk_2` FOREIGN KEY (`type`) REFERENCES `device_types` (`type`),
      CONSTRAINT `devices_ibfk_3` FOREIGN KEY (`IP`) REFERENCES `allocations` (`IP`),
      CONSTRAINT `devices_ibfk_4` FOREIGN KEY (`department`) REFERENCES `departments` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    Other than wanting confirmation on being able to use team-id as a primary key, which you can, I'm not sure what you're needing from us.

    When designing the DB, did you do that with normalization in mind?

    Are your tables InnoDB or MyISAM? You should be using InnoDB so that you can take advantage of foreign key constraints (for referential integrity) as well as other features it offers.

  • #3
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,944
    Thanks
    2
    Thanked 170 Times in 165 Posts
    I probably should clarify something. Since the team-id field is being used in multiple tables, it should be a primary key in only one of those tables. In all of the other tables it will be a foreign key. I don't know enough about your database to say if this is the table where it should be defined as the primary key.

  • Users who have thanked FishMonger for this post:

    MrBiggZ (05-18-2014)

  • #4
    Regular Coder MrBiggZ's Avatar
    Join Date
    Apr 2005
    Location
    Indianapolis IN
    Posts
    345
    Thanks
    44
    Thanked 2 Times in 2 Posts
    Sorry about that FishMonger.

    Remember at my best I'm at the novice level.

    Code:
    Table	"Create Table"
    teams	"CREATE TABLE `teams` (
      `ukey` int(2) NOT NULL DEFAULT '0',
      `team_id` varchar(18) DEFAULT NULL,
      `age_range` varchar(3) DEFAULT NULL,
      `sex` varchar(5) DEFAULT NULL,
      `team_name` varchar(13) DEFAULT NULL,
      `doc_path` varchar(200) DEFAULT NULL,
      PRIMARY KEY (`ukey`),
      UNIQUE KEY `team_id` (`team_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8"
    The GUI was phpMyAdmin but I bounce between that and MySQL Workbench.

    In my 20+ years in IT I used to write reports that where from DMSII databases (Unisys DMSII - Wikipedia, the free encyclopedia) so I understand the concept.

    Sometime the examples I follow and once I step back and look again it makes sense to do it another way!


    Thanks you for answering my question though!
    “No matter how slick the demo is in rehearsal, when you do it in front of a live audience, the probability of a flawless presentation is inversely proportional to the number of people watching, raised to the power of the amount of money involved.” ~ Mark Gibbs

  • #5
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,944
    Thanks
    2
    Thanked 170 Times in 165 Posts
    I created the team-id which is some random string.
    Hmm, I question that approach. Personally, I would not use "some random string". If you don't want to use sequential numbers, which I can understand, then the id should have some logical and easy to discern meaning rather than a random string.


  •  

    Posting Permissions

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