dcsimg
Results 1 to 8 of 8

Thread: [RESOLVED] Is a good idea using a string field as PK instead of an int in this case?

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2018
    Posts
    41

    Resolved [RESOLVED] Is a good idea using a string field as PK instead of an int in this case?

    Hi to all,

    I'm creating the database of a proyect and an idea has come to my mind: why using an additional int field if the only important field that is string must be unique??

    I've read opinions in both senses, some says it's good, some is wrong, but since my case is different to all I've read I want to ask here:

    I've a companyes table, but every company can be called by several names, I mean, user will define the name of the company, but also alternative names for it (alternative = 1 to n)

    So, I created the Company table and the CompanyNames:

    public class CompanyNames
    {
    public CompanyNames() { }

    public int CompanyNamesId { get; set; }
    [Required]
    [MaxLength(100)]
    public string Name { get; set; }
    [Timestamp]
    public byte[] RowVersion { get; set; }

    //Here I define the foreign key, so I can know what company does this name belongs
    public CompanyId { get; set; }
    public int CompanyId { get; set; }

    }
    In this way, when I have an string that can be a companyname I look for this name in the CompanyNames table, if found, I get the CompanyID so I can access to the company data that is on Company table.

    My idea, since the field Name must be unique (it has no sense havind duplicated names), is doing something like this:

    public class CompanyNames
    {
    public CompanyNames() { }

    [MaxLength(100)]
    public string CompanyNamesId { get; set; }
    [Timestamp]
    public byte[] RowVersion { get; set; }

    //Here I define the foreign key, so I can know what company does this name belongs
    public CompanyId { get; set; }
    public int CompanyId { get; set; }

    }
    In this way, when I look for a Company Name in the table Company Names i do it throroght the primary key, not another field.

    Is this a good or bad practise?

    Regards and many thanks
    Last edited by TassadarNET; May 19th, 2019 at 01:20 PM.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,372

    Re: Is a good idea using a string field as PK instead of an int in this case?

    There is certainly an amount of opinion involved either way so you won't be objectively wrong in either case. Personally, I tend to use a surrogate key in all cases, so a numeric identity or AutoNumber or the like. That way, there's never a reason to change the PK value. If you use a value that is actually part of the data, even if it's unique, there may be a reason to change that value after creation of the record, which means changing the PK value. It's not inherently wrong to change a PK value but it's something that many, if not most, people tend to avoid. I generally use SQL Server and, in cases like yours, I would use an identity column as the PK and then add a unique index to the CompanyName column.

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,255

    Re: Is a good idea using a string field as PK instead of an int in this case?

    The debate about natural vs surrogate keys rages on

    Personally, I quite like natural keys where they're appropriate. Sadly, they rarely are.

    Can you guarantee that a company name will ALWAYS be unique? I know you've said you can but stop and think on that because it sounds unlikely. Names are rarely unique and I certainly know of multiple companies that share a name.

    Will a company name never change? As JM has said, you can change the value of a primary key, but you really don't want to. It involves updating every record that refers to that primary (i.e. cascading). This gets really problematic because no matter whether you update the parent or the child first your database will be briefly inconsistent.

    Generally I've found that natural keys tend to work pretty well when they represent data that's native to and controlled the system - e.g. part numbers. They work badly when the data is not native and comes from the domain - you assume a company name is unique but unless you have total control over that it is an assumption.

    The main advantage of natural keys is that they aid readability of data but there are disadvantages too. E.g. you primary key will be supported by an index and an index on varchar is a lot wider than an index on an int. (Of course, a unique constraint will also require an index which would negate that benefit). Probably more important is the space a natural key will use up if it's referenced as a foreign key from several children.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4

    Thread Starter
    Member
    Join Date
    Jul 2018
    Posts
    41

    Re: Is a good idea using a string field as PK instead of an int in this case?

    Many thanks for the answers, jmcilhinney and FunkyDexter,

    Yesterday I was convinced on my mind that there would not be duplicate "Names" in the companyname table, but now I've read your comments and I realyze I was wrong. In fact, in some cases a company can exist twices in the table if has more than one delegation xD.

    So, there is no discussion in this way, I'll continue using surrogate keys.

    Now, my biggest "fear" comes in terms of performance, and also I don't know how to perform a query that takes the two tables, I'd thank a lot an advice for this. As I've say, in some cases there will be two companies with the shared name, so the result of this query:

    Select * from CompanyNames where Name="VB Forums Is Great Inc"
    will return more than one result.

    Is this the best way to get those values?

    Code:
    using (var contxt = new MyDatabaseContext())
                {
                    var query = from b in contxt.CompanyNames where b.Name.Equals("Happy Company Inc.") select b;
                    var Registers = query.ToList();
    
                    for (int Cont = 1; Cont <= Registers.Count(); Cont++)
                    {
                        MessageBox.Show(Registers[Cont - 1].Name);
                    }
                }
    This query takes the results of CompanyNames table, but what I really need are the results of Company table that have companies whose name is in Companynames. If i'm not wrong, in sql it was something like:

    Select * From Companies where CompanyId in (Select CompanyId from CompanyNames where Name="VB Forums Is Great Inc")
    Could someone tell me how this query would be using Code First??

    Many thanks!
    Last edited by TassadarNET; May 20th, 2019 at 04:45 AM.

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,372

    Re: Is a good idea using a string field as PK instead of an int in this case?

    Please keep each thread to a single topic and each topic to a single thread. If the question described by the title of this thread has been answered, use the Thread Tools menu to mark the thread Resolved. If you have a new question on a new subject, create a new thread with an appropriate title for that.

  6. #6

    Thread Starter
    Member
    Join Date
    Jul 2018
    Posts
    41

    Re: Is a good idea using a string field as PK instead of an int in this case?

    Quote Originally Posted by jmcilhinney View Post
    Please keep each thread to a single topic and each topic to a single thread. If the question described by the title of this thread has been answered, use the Thread Tools menu to mark the thread Resolved. If you have a new question on a new subject, create a new thread with an appropriate title for that.
    You're completely right, it does not help at all in having information correctly in the forum.

    I've open a new thread:

    http://www.vbforums.com/showthread.p...11#post5389111

    Regards and many thanks

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,255

    Re: [RESOLVED] Is a good idea using a string field as PK instead of an int in this ca

    will return more than one result.
    You are correct. It will return more than result. To avoid that you really need to think about your UI design. Typically what most of us would do is display enough information in the UI for the user to distinguish between the two entities (so you might include address, telephone number, contact name etc.) We would also hold the surrogate key values in the UI so that, when the user made a selection, we could query the database using the key value, not the descriptive value.

    If you want an example of how this is done, take a look at a listbox. It has two data-binding properties: ValueMember and DisplayMember (think I've got those names right but it's been a while). ValueMember is where the key value goes. DisplayMember is where the description goes to be displayed to the user.

    I'll post in your other thread about how to join tables.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8

    Thread Starter
    Member
    Join Date
    Jul 2018
    Posts
    41

    Re: [RESOLVED] Is a good idea using a string field as PK instead of an int in this ca

    Many thanks FunkyDexter,

    I have this in mind, when searching a company by its alias it will show a list to select from it if the alias belongs to more than one company.

    Regards

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width