Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / Tables structure
Finance forum
Unanswered

Tables structure

(0) ShareShare
ReportReport
Posted on by CU04051814-0
Hi,

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

Solution1:
Student Table with the following fields: StudentId, Name, Email  (StudentId unique index)
Teacher Table with the following fields: TeacherId, Name, Email  (TeacherId unique index)
 
 
OR
Solution2:
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)
 
OR
Solution3:
Users Table with the following fields: UserId, Name, Email, UserType  (UserId unique)  [UserType is enum: Student,Teacher]
 
OR
Solution 4:
we would need to do sth like DirPartyTable, DirPerson and DirOrganization? and make Student and Teacher table to extend UsersTable?
 
OR
Solution5:
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
    Hi,
    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,
    Kevin
  • Verified answer
    Arunraj Rajasekar Profile Picture
    Arunraj Rajasekar 1,731 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
    Hi,
    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,
    Kevin
  • Verified answer
    Anthony Blake Profile Picture
    Anthony Blake 2,201 Super User 2024 Season 2 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,834 Super User 2024 Season 2 on at
    Tables structure
    Hy,
    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 230,468 Most Valuable Professional 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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,759 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,468 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans