Database structure advise

From: Ertan Küçükoğlu <ertan(dot)kucukoglu(at)1nar(dot)com(dot)tr>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Database structure advise
Date: 2017-04-23 10:48:45
Message-ID: 015101d2bc1f$2ed736f0$8c85a4d0$@1nar.com.tr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nicolas Paris 2017-04-23 11:04:50 Re: Database structure advise
Previous Message Ron Ben 2017-04-23 10:31:29 Re: Not sure this should be asked here but...