Shaping your database for Polar Studio

Polar Studio operates as a database-focused development environment, aligning with foundational concepts inherent to relational databases. This includes, but is not limited to, tables, fields, primary keys, and relationships. It's worth noting that, as of now, Polar Studio is designed to be compatible exclusively with Microsoft SQL Server. This encompasses a range of its versions, from SQL Server Express and SQL Server Web Edition to the Azure-hosted SQL database.

In utilizing Polar Studio, developers retain the freedom to construct their own data models. There's no imposition of specific data architectures, potentially allowing a straightforward integration or adaptation of pre-existing databases.

Database Import Procedures

The management environment allows you to import existing databases. We support both SQL Server backup files (.Bak) and Microsoft Access MDB files. The Microsoft Access files will be fully converted to SQL Server including indexes and foreign keys.

Data Model Considerations

While Polar Studio is very flexible regarding the data model, there are a few limitations that must be met to have a correctly working application:

• Every table must be designated a numeric (INT / BIGINT) primary key. While the recommendation leans towards the use of an autonumber (IDENTITY) system, it's not a mandate. In scenarios requiring alternate keys (e.g., a specific CustomerCodeNumber), one can simply append an additional unique index for such secondary keys.
• Given the numeric nature of primary keys, foreign keys adopt the same numeric format (INT / BIGINT).
• If you want to create a Many-to-Many relationship, such a table necessitate a singular numeric primary key coupled with a secondary key. For illustrative purposes, if there's a need to log which customers are associated with specific countries, the table construct would be: CustomerCountryID Int Not Null IDENTITY(1,1), CustomerID Int Not Null, CONSTRAINT FK_CustomerCountry_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID), CountryID Int Not Null, CONSTRAINT FK_CustomerCountry_CountryID FOREIGN KEY (CountryID) REFERENCES Country(CountryID) CREATE UNIQUE INDEX IDX_CustomerCountry_SecondaryKey On CustomerCountry(CustomerID, CountryID)
• Using queries/views: You can import queries/views in Polar Studio as if they are tables. To do so, these views must basically return the data of one table (the 'base table') and add additional (read only) data of other tables to it. Polar Studio facilitates these queries to be used without explicitly defining these in the database, by creating a query like this within Polar Studio. A query could be like: Select OrderLine.*, Product.Price As ProductPrice, OrderLine.Quantity * Product.Price As TotalCost FROM Product Right Join OrderLine On Product.ProductID = OrderLine.ProductID

Advantages of Value-list Domains over Lookup-tables

In the software development realm, numerous tables principally serve lookup purposes. While some remain immutable, others exhibit dynamic characteristics. Value-list domains are a viable alternative to small, static tables, examples being Yes/No or Gender classifications. A noteworthy mention is the enhanced multilingual support furnished by value-list domains compared to traditional lookup tables.