locked
Identity designer when using external SQL database. RRS feed

  • Question

  •  

    In the Beth Massi videos I've seen, Beth is working with the intrinsic database and she mentions that creating a field called id which she defines as Integer and required, is considered Best Practices in database design.

    I am working with an existing external SQL database and I constantly run into problems creating relationships. If it isn't the "Relationships must be mapped to key fields and only key fields in the Primary" that gets me it's the "Multiplicity" that spoils the day. Suffice it to say that creating relationships within LightSwitch while working with an existing external database is no day at the beach.

    So when I needed to add a few new tables to the existing database I thought it would be a good idea to leave the data out of the primary key and create a field named id for the key and set it to integer. That kinda worked OK until I had to create a relationship between two tables I had created that way (primary key = id = integer). Long story short... I didn't have any better luck creating relationships between tables that didn't use actual data fields as Primary keys versus tables that did. (Yes, I did set the identity property).

    So my question is... what is the generally accepted best way to create relationships in LightSwitch between tables in an external SQL database? It doesn't appear to be a straight-forward process and often causes me to not only need to put extra code in the programs, but also play around with adding fields to the existing database which more often than not must be required which means I need to write SQL procedures to propagate data into new fields that don't allow nulls.

    There's gotta be a better way. :)

    Thanks for listening (err... reading)

     
    Sunday, January 27, 2013 2:25 AM

Answers

  • Following best practices, every table should have an integer primary key (PK) column. Any time you want to relate that table to another table, you add an integer foreign key (FK) column.

    The relationship gets created using the FK value of the table you'll be relating to another table, & the PK of the related (or "lookup" table).

    It's VERY simple. If you do this, LightSwitch will automatically model those relationships for you, when you attach the database as an external data source.

    Example:

    Customer table

        - ID (Integer, Identity = true)

        - Name (varchar)

        - Gender_ID (Integer, Identity = false)

    Gender table

        - ID (Integer, Identity = true)

        - Name (varchar)

    In the Customer table, create a relationship, using its ID PK column, & the Gender table's Gender_ID FK column.

    When you attach the database as an external data source, the Customer entity, automatically will have a Gender navigation Property, & the Gender table will automatically have a Customers navigation collection property.

    I hope that makes it a bit easier to understand?


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    • Marked as answer by Jyuma1 Tuesday, January 29, 2013 10:31 AM
    Tuesday, January 29, 2013 5:40 AM
    Moderator

All replies

  • Create tables in SQL server using integer PKs.  Relate the tables in SQL server (example).  Connect LS to the external database and enjoy.  If the SQL server db is set up correctly you should not need to do anything in the LS entity relationship designer.


    • Edited by Hessc Sunday, January 27, 2013 3:29 AM
    • Proposed as answer by Yann DuranModerator Sunday, January 27, 2013 5:05 AM
    Sunday, January 27, 2013 3:25 AM
  • Thanks... I'll give it a try.
    Sunday, January 27, 2013 3:54 AM
  • Thank you for your assistence.

    I tried doing what you suggested and I managed to move my problem from the Entity Designer in LightSwitch to the Create Relationship Table Designer in SQL, but the problem remains basically the same.

    Unless I'm missing the point entirely, it appears that you must use the PK on the primary side of the relationship and therein resides my problem. When the primary side of the relationship does not have the same number of fields and data types that comprise the key on the foreign side, the designer throws an error.

    Maybe if I give a simplified example you may be able to show me where I am making my error.

    Table "A" has a PK id that is integer and defined as Identity. It contains a single column named Client that is nvchar.

    Table "B" has a PK id that is integer and defined as Identity. It contains 2 columns, the first is "Client" and the second is "Account". Client is nvchar and Account is Integer.

    I am attempting to create a One to Many relationship between the "Client" field in Table "A" and the "Client" field in Table "B". Apparently that can't be done. I tried creating a One to Many relationship between the "id" fields of the two tables and both the Entity Designer in Lightswitch and the Table Designer in SQL allow me to do that but LightSwitch then throws the error "id is not a member of LightSwitch project" (or something like that, I forget the exact words).

    If I abandon the "id" field concept and go with the data fields being key, I run into the problem of not having the same number of key fields in both tables. If I add a dummy field to table "A" just to satisfy the number of key fields requirement, I then run into the multiplicity issue of One to Many using only required fields... and so on.

    I must be missing something. Maybe what I want to do can't be done, at least not in the way I am attempting to do it.

    Sorry for the lengthy response but I am attempting to consume 3 new disciplines all at the same time and I don't even know what I don't know.

     

         

    Sunday, January 27, 2013 1:15 PM
  • Hi Ed,

    Wherever possible, use an integer PK & therefore integer FK's. LightSwitch simply doesn't support composite keys anyway (ie keys made up of multiple properties). You can sometimes get away with a key type that's not an integer, but especially in LightSwitch you should try to always use integer keys. The SQL data type to use is an auto incrementing integer, also called an identity column.

    If you can't create the relationship in the database itself, that's a very good indication that there's something wrong with the schema, or that you're trying to join tables on properties of different data types.

    Whenever you make a change in an attached data source, make sure you remember to "update" the data source in LightSwitch, or else LightSwitch won't know about your schema changes.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Sunday, January 27, 2013 2:13 PM
    Moderator
  • @Yann... I guess I'm having a difficult time visualizing how LightSwitch will know what fields relate to what fields if I create the relationship between two tables by using an auto increment id field that contains no useful data and is really nothing more than a sequential integer number.

    Is the sailient point actually the fact that the tables are where the relationship is created and not the data that is in the tables?  But if that's true then how do parent child records come together if LS knows nothing about the data?   

        

     
    Sunday, January 27, 2013 4:26 PM
  • I think you are not relating the tables correctly.

    Table A

    ClientId, int, PK

    Client, nvarchar

    Table B

    AccountId, int, PK

    Account, varchar

    ClientId, int, FK

    Table B has a field (FK) that maps to table A (PK), that is how you make the FK column.  Next, click on the relationship editor within sql server and relate the two fields.  Then it will work fine in LS.

    There is an abundance of reference material available for designing tables and relationships.  Here are a couple links, but use bing, google or you tube and you will find hundreds of tutorials and samples.

    http://msdn.microsoft.com/en-us/library/ms175464(v=sql.105).aspx

    http://www.youtube.com/watch?v=4q-keGvUnag


    • Edited by Hessc Monday, January 28, 2013 5:39 AM
    Monday, January 28, 2013 1:50 AM
  • Thank you for your very helpful advice.
    Tuesday, January 29, 2013 1:53 AM
  • Following best practices, every table should have an integer primary key (PK) column. Any time you want to relate that table to another table, you add an integer foreign key (FK) column.

    The relationship gets created using the FK value of the table you'll be relating to another table, & the PK of the related (or "lookup" table).

    It's VERY simple. If you do this, LightSwitch will automatically model those relationships for you, when you attach the database as an external data source.

    Example:

    Customer table

        - ID (Integer, Identity = true)

        - Name (varchar)

        - Gender_ID (Integer, Identity = false)

    Gender table

        - ID (Integer, Identity = true)

        - Name (varchar)

    In the Customer table, create a relationship, using its ID PK column, & the Gender table's Gender_ID FK column.

    When you attach the database as an external data source, the Customer entity, automatically will have a Gender navigation Property, & the Gender table will automatically have a Customers navigation collection property.

    I hope that makes it a bit easier to understand?


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    • Marked as answer by Jyuma1 Tuesday, January 29, 2013 10:31 AM
    Tuesday, January 29, 2013 5:40 AM
    Moderator
  • Yes it does.  Thank you.  
    Tuesday, January 29, 2013 10:29 AM
  • Yann

    Sorry but integer is not the best option for all situations, i like more use GUIDs, is better if you plan to use sql replication in tthe future.


    Norman

    Tuesday, January 29, 2013 12:51 PM
  • Maybe, but I think the salient point that's being made is that you should use some kind of easily indexable item as your primary key (be it integer or GUID) which is unrelated to the rest of the data in the column.  Indexing on strings or other data within your table which you think will always be unique is bad practice.


    Free Visual Studio LightSwitch extensions: Elyl's Extensions

    Tuesday, January 29, 2013 2:12 PM
  • ElyIV

    remember the option is better only if you planning to use sql replication in the application


    Norman

    Tuesday, January 29, 2013 2:25 PM
  • OK... I'm sold... be it Integer Id or GUI Id, anything is better than data. :)

    Unfortunately I'm forced to work with an existing schema that already uses data fields (and plenty of them) as the primary keys along with additional sequence number keys just to keep the others unique. Quite ugly indeed. ;)

    Tuesday, January 29, 2013 2:37 PM
  • As I stated before, LightSwitch does not support composite keys. Sure Norman, a GUID is also an alternative to an integer, but as you've just pointed out, ONLY really helpful if replication is going to be involved. They are much harder to deal with than integers. I was talking mainly from a LightSwitch perspective, where integers will give far less problems than anything else, & are much easier for those new to designing data schemas.

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Tuesday, January 29, 2013 4:00 PM
    Moderator