Database

This section enlists some of the best practices related to databases, schemas and their relation to LLBLGen Pro. These best practices help you get better results with LLBLGen Pro.

  • Give your tables a primary key. The primary key fields will be fields you can use to instantiate an entity back into memory. Specifying which fields form the primary key (PK) of an entity will enhance the generated code.
  • Give your tables singular names, not plural names. An entity is a single unit, like Customer. The definition of an entity is determined from the definition of the table (or view) it's mapped on. If you name your table 'Customers', you will end up with the entity 'Customers', unless you change the name of the entity in the Add Entities form catalog screen, which of course takes time. It's best practice to name your tables after the entity they represent, which is a singular name, like Customer or Order. For views you want to use as entities, the same applies. LLBLGen Pro supports singularization through Designer Event plug-ins, however these are for English only. To avoid any extra work, define your tables with singular names.
  • Do not prefix your table/view names. LLBLGen Pro supports prefix/suffix stripping, to strip off the prefixes and suffixes of table / view names and fields, which would mitigate the fact that everything is prefixed anyway, and if you keep the prefixes, the entity classes will have non-standard names, likely not what you want to use. If you don't strip off the prefixes from entities for example, you've to rename the entities in the designer which might be more tedious than you would expect.
  • Define all foreign key constraints. Because relationships are very important to walk the schema from one entity to another, it is very important that you define the foreign key constraints (relationships) in your database. This way, LLBLGen Pro can determine which relationships are present between the different entities and will add these relationships in the project automatically. Although it's perfectly possible to define a relationship in the LLBLGen Pro designer, it is common practice to include foreign key constraints for data integrity. Some developers leave them out during software development, because data has to be removed sometimes during testing, make sure the foreign key constraints are present when you create the project or refresh the catalog(s).
  • Do not define more than one foreign key on a single non-PK field. Some people define more than one foreign key constraint on a single, non-primary key field and point these to two or more different table's primary key fields. This is bad schema design, even though SQL Server (and other databases) allows you to do this: LLBLGen Pro can't determine this way to which table (entity) the entity with the multi-fk constraints on one field relates to, using the field with the fk constraints.
  • Define unique constraints on columns with unique values. Unique constraints are not always used on fields which contain solely unique data. Because LLBLGen Pro will generate extra fetch functionality for entities, the generated code will have more functionality when you add unique constraints to fields in your database (a unique constraint can span more than 1 field).
  • Do not prefix your table field names. Because table field names will be used initially to form the names of the field names in the entities, it is best you do not prefix them with things like f_ or other prefixes, because you probably want to remove them later in the designer. LLBLGen Pro can strip them off for you, though best practices say field names shouldn't be prefixed.
  • Give the table fields proper names. Fields like id_1 or other cryptic field names are not very helpful as entity fields. Give your fields proper names so you don't have to rename a lot of fields in the designer.
  • Design your concurrency scheme before you start creating a LLBLGen Pro project. LLBLGen Pro supports all concurrency schemes thinkable using the IConcurrencyPredicateFactory interface you can implement to specify per-entity concurrency control. If you want to use timestamp columns for example, be sure you define these up front in the schema.
  • Do not confuse a unique index with a unique constraint. A unique constraint is part of your relational model, it defines a constraint defined in the model, no matter how many records are stored inside the model. A unique index is a combination of an index and a unique constraint. Some database systems define this construct. It's not the same as a unique constraint: an index is not part of the relational model. An index can be added or removed based on the amount of data stored in the table. This also means that an index is applied most of the time when the system is in production, and not during development. LLBLGen Pro recognizes unique constraints but not unique indexes. If you want to use the unique constraint functionality in LLBLGen Pro, define a unique constraint. If you need extra index functionality of the database system, define also an index.
  • Define the 1:1 relations for an inheritance hierarchy in a chain. For a hierarchy of type TargetPerEntity, every entity is mapped onto its own table/view and the subtypes have an 1:1 relation from their PK to the PK of their supertype. It's therefore key that you define in the database foreign key constraints from the PK of the subtype to the PK of the supertype of that subtype. So in the hierarchy Employee <- Manager <- BoardMember, a foreign key constraint is defined from BoardMember's PK to Manager's PK and from Manager's PK to Employee's PK.
  • Consider a view instead of a myriad of relations. Often the schema of a legacy database isn't all that great and to utilize it in full with LLBLGen pro, it sometimes takes a bit of a challenge as entities are scattered all over the place and to fetch all data in one entity, it might be that inheritance hierarchies have to be created which are simply unnecessary. To overcome this, define views in the database schema utilizing the tables at hand, and map entities onto these views. Be aware that inserting / updating entities mapped onto views is only possible if there's an insert / update trigger in-place for the view, however in a lot of cases you can map additionally an entity on each table in question, using that for data manipulation and use the view for data fetch scenario's.