Skip to main content
Finance forum

Tables structure

editSubscribe (0) ShareShare
Posted on by 90

I'm trying to figure out the best way to build tables in D365
let's say we have teachers and students
What's the best way to build the new tables

Student Table with the following fields: StudentId, Name, Email  (StudentId unique index)
Teacher Table with the following fields: TeacherId, Name, Email  (TeacherId unique index)
Users Table with the following fields: UserId, Name, Email  (UserId unique)
Student Table with the following fields: StudentId, UserId   (relation with UsersTable)  two indexes: (StudentId unique index) (UserId unique Index)
Teacher Table with the following fields: TeacherId, UserId    (relation with UsersTable based on UserId)  two indexes: (TeacherId unique index) (UserId unique Index)
Users Table with the following fields: UserId, Name, Email, UserType  (UserId unique)  [UserType is enum: Student,Teacher]
Solution 4:
we would need to do sth like DirPartyTable, DirPerson and DirOrganization? and make Student and Teacher table to extend UsersTable?
Would we need related field fixed and field fixed relations?

Additional question: in general for UserId, StudentId and TeacherId. Would we need integer EDTs and auto generate them by code? OR  would we need number sequences? when should we decide to choose integer EDT or use number sequence? (please note this is just a practice example and not an actual solution)
  • Kevin Xia Profile Picture
    Kevin Xia Microsoft Employee on at
    Tables structure
    Has your problem been solved? If so, you can mark the thread as verified so that other users of the forum can view the post.
    Best regards,
  • Verified answer
    Arunraj Rajasekar Profile Picture
    Arunraj Rajasekar 1,729 on at
    Tables structure
    Solution 1: If there are no shared features between the two tables, this is a straightforward solution. The student table will have a unique student ID, while the instructor table will have a unique teacher ID.

    Solution 3.
    This technique is useful when you have common functions for both types of data.

    Solution 4
    As previously said, this solution will allow you to use basic features such as the Global Address Book and Dirparty.

    Use integer EDT for ID fields if the unique ID merely contains a number and does not need to be sequential.
  • Verified answer
    Kevin Xia Profile Picture
    Kevin Xia Microsoft Employee on at
    Tables structure
    Judging from the information you have provided so far, solution 3 is the best. If there is really only one difference in user type between teachers and students, and the fields that need to be stored are all unified and unchanged, and there are no additions based on user types, then choose solution 3, which is the easiest and fastest.
    Best regards,
  • Verified answer
    Anthony Blake Profile Picture
    Anthony Blake 758 on at
    Tables structure
    Why are you basing it on Users? It depends on the context, but take a look at workers, very similar to solution 3. Or make it like a customer or vendor, it totally depends what the requirement is.
  • danielstones Profile Picture
    danielstones 33 on at
    Tables structure

    For building tables in D365 for a scenario involving teachers and students, the best approach depends on your specific requirements, but here's a brief overview of each solution:

    Solution 1: Keeps student and teacher data separate. Simple and straightforward, but doesn't allow for easy expansion if users can have multiple roles (e.g., a user who is both a teacher and a student).

    Solution 2: Introduces a common Users table, which is a more normalized approach, reducing redundancy and making it easier to manage common data (like name and email). It's suitable if you anticipate needing to manage users more generically across the system.

    Solution 3: Similar to Solution 2 but uses a UserType field to distinguish between students and teachers. This is efficient for small distinctions but can become cumbersome if students and teachers have many unique fields or functionalities.

    Solution 4: Suggests a more complex, extendable structure possibly using Dynamics 365's built-in tables like DirPartyTable, DirPerson, and DirOrganization. This is the most flexible and scalable option, especially if integrating deeply with other D365 features, but also the most complex to implement.

    Solution 5: Seems to refer to establishing relationships and data integrity through database design. It's essential but needs more context to be evaluated properly.

    Regarding UserId, StudentId, and TeacherId:

    • Integer EDTs are simple and can be auto-generated by code, suitable for smaller datasets or when you have simple identifier needs.
    • Number sequences are more robust, providing a system-managed, sequential numbering system that ensures uniqueness and is better for larger datasets or when legal or business requirements dictate specific formats for IDs.

    Deciding between integer EDTs and number sequences often comes down to the scale of your application, the need for sequential, predictable numbering, and any specific business or regulatory requirements you must adhere to. For practice, starting with integer EDTs is simpler, but for a production environment or where ID consistency and predictability are important, number sequences are usually the better choice.

  • Verified answer
    andreasraithel Profile Picture
    andreasraithel 4,382 on at
    Tables structure
    I understood this just a smaple solution.
    Anyway there are so many fields and options to classify organizations as well as persons. Because of this I always try to avoid to create new tables.
    With GAB concept and all options around contact person, organizations and relationships, why to create even more complexity when similar entities already exist.
    For sure new tables will be necessary for data entities we do not have in F&O, but the design of the new solution depends from the required processes, not only the data to store.
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 222,703 Super User on at
    Tables structure
    There is no universal solution - it depends on your requirements.
    For example, solution 3 is good if students and teachers will always have the same set of fields, but it's wrong otherwise. All most of your suggestions are wrong if you want to be able to track more than one e-mail. Having a single field for names may be insufficient. (DirPerson would help with these things).
    Don't ask us whether you want to generate numbers from a number sequence. Ask your business users instead.

Helpful resources

Quick Links

New Blog Features Released!

Check out the new community blog features for viewers and authors…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…


Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,166 Super User

Martin Dráb Profile Picture

Martin Dráb 222,703 Super User

nmaenpaa Profile Picture

nmaenpaa 101,138

Featured topics

Product updates

Dynamics 365 release plans