Finance forum

# Tables structure

Subscribe (0)
Report
Posted on by 90
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)
Attachments
• 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
• 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.

• 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
• 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 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.

• andreasraithel 4,382 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.
• 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).

#### 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â€¦

#1

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

#2

Martin DrÃ¡b 222,703 Super User

#3

nmaenpaa 101,138