Navision sql table structure and Future Dynamics Product

NavStudent

Hello
From what I understand is that in Native db the company is part of the table and all the companies are in the same table.
On sql every company has a separate table. So My question is why did Navision chose this solution, instead of keeping Company Name part of PK of the table?
Also how does other erp system handle this, and When Dynamics comes out several years, which approach will they take?

Thank you.

SteveO

I think that one of the main reasons is related to the SIFT tables.
Because SQL has no support for SIFT, the values are maintained in seperate tables in SQL. If there was no distinction between companies at table level then all companies would use the same SIFT tables and make this a major bottleneck (and it's bad enough as it is).

Perhaps it's also because a lot of Navision tables are over-indexed and by seperating the tables out each company gets its own set of indexes to maintain, to minimise the impact on the other companies. So that inserting a record in company A theoretically shouldn't have that much of an impact on companies B, C and D, if they are all using the same tables (eg. Sales Orders). Also, if all the companies existed in the same table you could get boundary locks for a different company (eg company A locks a record in company . I would think that would be a serious flaw.

I think other ERP systems split different companies into completely different databases. So 2 companies = 2 databases.

I would hope that Microsoft adopts this approach in future releases because it would make using things like Reporting Services a breeze (Currently you have to know specifically what the company name is to know what the Name of the table is, and then it's fixed to that specific company name, unless you want to write some dynamic SQL ).
Just my .02


edit-- Actually Native DB also splits the companies into different tables.
You have 2 types of Table objects, Table (the table structure) and TableData (the actual data held within the table). If you look in the object table you will see that Table is not specific to a company (all tables of the same ID share the same structure) and that TableData is always specific to an individual company (unless DataPerCompany on the Table in question is set to No). AFAIK, SQL doesn't support this type of seperation between Table Structure and Table Data, or perhaps to make it work like this from a Navision perspective would have lead to too many problems for it to be feasible.

NavStudent

If you have multiple database , How are you going to inter company posting? Object synchronization for customization? Security setup?
If MS is not going to implement SIFT, then Dynamics ERP will look nothing like Navision.

SteveO

They could write scripts to handle synchronising the table structure and security across the various databases.

Because it is going to be a 3 tier architecture, the objects (business processes, form layouts?? etc.) will be run on the middle tier (IIS) and not the SQL database. So the business processes and data are actually entirely seperate entities.

Intercompany postings etc. This could all be handled by the middle tier. eg ExampleOfICPosting(From Company, To Company, PostingDetails). You're essentially posting 1 transaction in 1 database and 1 transaction in a seperate database. This would most likely be abstracted away from us (NAV developers) so we will just have to worry about calling a function (just like we currently do).

Not much will really change from a NAV development point of view for quite some time.

Dynamics NAV 5.1 is being released (hopefully!!) next year and we'll get a better picture of what Microsoft is working towards. But I personally think that they are going to adopt the approach that most of the other ERP systems do. (Traditional 3 tier. Client (Presentation) -> Business Process (Execution) -> Database (Storage)

Also who knows what advancements are going to be made in the next couple of years. Maybe something happens that completely turns the computing world on its head? So it's nearly impossible to predict how a single, unified Dynamics product would look (if MS even decide to go down that route)

NavStudent

I see
Having the objects in service tier makes sense. Or they could have a sparate database for the Busines logic.

I hope they take into consideration offline feature for Dynamics. TCP/IP protocol was build on the assumption that the network are not reliable.
Using ERP system on top of it should also take that into consideration as well.



What about building cubes and for analysis services, Wouldn't it make it harder to build them accross database especially if you are doing consolidation?

davmac1

In SQL Server, multiple databases are not a problem.
If Navision moves to one database per company, then the database name would be the company id. Referring to tables for another company would be qualified by the database name.
Common tables could be kept in a "COMMON" database and Navision system tables could also be kept in a separate database.
The only hassle for developers would be that separate versions for different organizations would need to be kept in a separate database instance.
Another, possibly better way, would be to allow each company to point to its own COMMON and SYSTEM databases.
Then beginners can get horribly confused....

kriki

[Topic moved from Navision forum to SQL General forum]

njaiku

Hi,

I gone through the solution which you have provided. It's very valualable...
Presently my client wants to use SQL Analysis services and to get the report through that.
I have connected to the NAV 4.0 SP3 DB and the connection everything is fine.
When i view the datasource view i am getting multiple table with multiple company name
ex If the database consist of three companies A, B , C i can view it as A. Customer,B.Customer,C.Customer...

At the end of the result in the cube i got the result for only one company. But the client needs the consolidation ...

A big problem is in front of my eyes... i try hard to find the solution through the web...

Is it there any way... could you plz help me out