|
|
|
 |
Database design and the creation of an entity relationship diagram, also known as an "ERD" or data model, is an important yet sometimes overlooked part of Excel database integration and use.
An accurate and up-to-date data model serves as an important reference tool for developers and other members of a joint application development team who work together to create the integration between Excel, the Excel application or applications, Excel Macros, and the database or data source which holds, manages and maintains the data and information that is relayed and revised.
Effective database design allows for the development of Excel applications that perform well from the beginning. The database design and use of database design services builds quality and quality assurance into the project, enabling our development team to reduce the overall time it would otherwise take to complete the project, which in turn reduces overall project development costs.
Undertaking the process of database design and creating a data model helps you as well as the development team better understand the user requirements and enables our database and Excel application development engineers to build a system that is more reflective of the user requirements and business rules.
In combining our expert knowledge, awareness, and understanding of Microsoft Access, SQL and SQl databases. Database design, replication, and integration with Excel. Our database engineers consult with you to determine your database needs and requirements. Then design, and create your unique database installations that integrate directly into your Excel applications and interfaces. Simply contact us today outlining your specifications for a free quotation.
|
|
|
|
 |
|
|
|
System deifnition and entity relationship diagrams.Design and creation of Microsoft Access databases.Design and creation of Micosoft SQL databases.Normalization of existing databases.Updating of existing database fields and data types.Minimization of data redundancy.Minimization of data restructuring. |
|
|
 |
When designing a database and planning your database design. Our database engineers work with all recognized standards and techniques that constitute effective database design.
An example of aspects of good practice and successful database design can be seen as:
Entity Standards
An entity is a logical collection of things that are relevant to your database. The physical counterpart of an entity is a database table. All database entities are created in singular form and in ALL CAPS. For example, an entity that contains data about your company's employees would be named EMPLOYEE.
Attribute Standards
An attribute is a descriptive or quantitative characteristic of an entity. The physical counterpart of an attribute is a database column or field. All database attributes are entered in singular form with either Initial Capital Letters or in all lower case. For example, some attribute names for your EMPLOYEE entity might be: EmployeeId, or employee_id, and BirthDate, or birthdate.
Primary Key
A primary key is an attribute or combination of attributes that uniquely identify each instance of an entity. A primary key cannot be null and the value assigned to a primary key should not change over time. A primary key also needs to be efficient. For example, a primary key that is associated with an Integer or INT datatype will be more efficient than one that is associated with a Character or CHAR datatype. Primary keys should also be non-intelligent and their values should be assigned arbitrarily without any hidden meaning. Sometimes none of the attributes of an entity are sufficient to meet the criteria of an effective primary key. In this case the database designer is best served by creating an artificial primary key.
Relationships (One to Many)
A relationship is a logical link between two entities. A relationship represents a business rule and can be expressed as a verb phrase. Most relationships between entities are of the "one-to-many" type in which one instance of the parent entity relates to many instances of the child entity. For example, the relationship between EMPLOYEE and STORE_LOCATION would be represented as: one STORE_LOCATION or parent entity, employs many EMPLOYEEs or child entities.
Relationships (Many to Many)
The second type of relationship is the "many-to-many" relationship. In a "many-to-many" relationship, many instances of one entity relate to many instances of the other entity. "Many-to-many" relationships need to be resolved in order to avoid data redundancy. "Many-to-many" relationships may be resolved by creating an intermediate entity known as a cross-reference or XREF entity. The XREF entity is made up of the primary keys from both of the two original entities. Both of the two original entities become parent entities of the XREF entity. As a result, the "many-to-many" relationship becomes resolved as two "one-to-many" relationships. For example, the "many-to-many" relationship of many EMPLOYEEs are assigned many TASKs which can be resolved by creating a new entity named EMPLOYEE_TASK. This resolves the "many-to-many" relationship by creating two separate "one-to-many" relationships. The two "one-to-many" relationships are EMPLOYEE or parent entity which is assigned EMPLOYEE_TASK or child entity and TASK or parent entity is assigned to EMPLOYEE_TASK or child entity. Whilst this may appear complex, the introduction of the EMPLOYEE_TASK child entity reduces data redundancy and improves overall database and application performance.
Relationships (Identifying and Non-Identifying)
Relationships between two entities may be classified as being either identifying or non-identifying. Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. On the other hand, a non-identifying relationship exists when the primary key of the parent entity is included in the child entity but not as part of the child entity's primary key. In addition, non-identifying relationships may be further classified as being either mandatory or non-mandatory. A mandatory non-identifying relationship exists when the value in the child table cannot be null. Whilst a non-mandatory non-identifying relationship exists when the value in the child table can be null.
Foreign Key
A foreign key exists when the primary key of a parent entity exists in a child entity. A foreign key requires that values must be present in the parent entity before like values may be inserted in the child entity. The concept of maintaining foreign keys is known as "referential integrity".
Cardinality
Cardinality determines the nature of the relationship between the child entity and the parent entity. The cardinality of a relationship may be determined by asking the following question: "How many instances of the child entity relate to each instance of the parent entity?".
There are four types of cardinality: - Common cardinality defined as one to zero.
- P cardinality defined as one to one
- Z cardinality defined as one to zero
- N cardinality defined as one to exactly N
By maintaining and adhering to recognized international standards in database design and creation, 123XLSX.com is able to design and create the optimal database and data structures for your Excel applications and back office needs.
|
|
 |
As a part of 123OfficeMedia.com, 123XLSX.com, was devised, developed and created as a specialist division to work entirely within Excel spreadsheet, dashboard and Excel application development.
Any successful Excel application relies upon a data source. Whether from databases such as Microsoft Access, Microsoft SQL, or mySQL. Or from text files, comma delimited files, or other Excel documents and spreadhseets. A data source provides the ability to reference, update, save and maintain data.
Once your database design is complete, integration of your database into your system, back office or external hosting partner is necessary to establish data connection to your database for your Excel applications. 123XLSX.com provide full database integration services to ensure that your database conforms to and integrates seamlessly within your existing architecture and data storage system.
Successful integration of your database allows your Excel application as well as other back office systems and applications, if defined and required, the ability to communicate and connect directly to the data source and access your application data.
|
|
 |
To find out more about 123XLSX Custom Excel Services, and how our team of Excel designers, programmers and office professionals can help you make the most out of your Excel documents, and spreadsheets and improve your back office efficiency. Please contact us today for a free no obligation quote and find out why more and more companies across the globe are turning to 123XLSX.com to deliver their Excel, spreadsheet and back office tools. |
|
|
|
