Hello All,
Using PostgreSQL 9.6.2 on a Windows 64bit platform.
I am about to start a new software development dealing with warehouse
operations. Software should handle multi-company structure. There will be
single company starting to use the software at the beginning.
I need to decide between below scenarios before I develop any database
structure.
Biggest table I am assuming will be inventory movements table. I am
expecting about 90% of the queries will run on that table. I do not see that
table to have more than 100.000 rows in a year for a single company. I am
not planning to have a new database for each year. Data will pile up in
time.
My current scenarios are:
1) Separate company records by adding a field in each table and keeping all
records in a single database and schema. (Queries will be easier to build
and run)
2) Separate company records by adding a new schema for each company there
may be keeping all records in a single database. (Queries will need UNION
across schemas for corporate reports)
3) Separate company records by creating a new database for each company.
(This option, I do not want to do. Will think about corporate reports if I
have to select this one).
I appreciate any suggestion on above possible options. New suggestions,
experience sharing are always welcome.
Thanks & regards,
Ertan Küçükoğlu