Three LightSwitch Database Design Tips

In a previous job, when we'd build LOB applications, we'd start with an understanding of the data, and build out the database, then the application. In LightSwitch, you again start by building a model of your database and work up to your application. In the little amount of time I've worked with LightSwitch, I've found three things to keep in mind when designing your databases. There will no doubt be more tips, but here are my first three: Tip 1: SQL Express is the default LightSwitch can consume data from a number of sources, but if you use LightSwitch to design your database, your database will be created in SQL Express. This will more than likely lead to version issues if your application is deployed to a number of machines. It may require SQL Express to be installed, which may not always be feasible or desired. My personal preference would be to use SQL CE as the database if an application is to be distributed, or full scale SQL Server if the app is to be published and used online. Tip 2: Don't design foreign key columns When designing databases in SSMS, we'd include a foreign key column when we designed the table, then add a relationship between the tables. For instance, in an address table, we might have a CountryId, which would be keyed to the primary key column in the Countries table. With Entity Framework's model-first paradigm, which is used by LightSwitch, foreign key columns are automatically added after you define a relationship. So skip the foreign key columns in the table design phase--they'll automatically be added when you add relations between the tables. Tip 3: Changes are Destructive If you're using the default database option, get your database designed as fully as you can as early as you can. Any changes you make are destructive to the database, and will destroy your data. If you need seed data, you'll need to include a routine to load the database after it is created. There is a management studio for SQL Express, so you can edit tables outside of the Visual Studio designer. If you're using full SQL Server, the permissions may not be in place to destroy a schema, so you may end up having to design your database in SSMS. Depending on your background, this may be more comfortable anyway. LightSwitch can also utilize the database-first paradigm, so it's OK to start with a database and build on it. I hope these help--let me know what you build!