Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgresql database

Postgresql database

From
Trinath Somanchi
Date:
Hi All,

I have Tree structured database to maintain.

Its as follows.

Vehicles 
|__> Two Wheelers
      |__> Manufacturer
            |__> Name of Manufacturer
            |__> Year of make.
            |__> Manuf. Plant.
            |__> Model.
                  |__> Model Name
                        |__> Model Version
                        |__> Model Re-make Version.
                  |__> Year of Release
                  |__> Serial Number  
                  |__> Capacity
                  |     |__> Capacity Type
                  |     |__> Capacity Version
                  |__> Contact Information
                        |__> Company Name
                        |__> Company Address
                        |     |__> Street Address
                        |     |     |__> Lane 1
                        |     |     |__> Lane 2  
                        |     |__> City
                        |     |__> Pin
                        |__> Contact Information
                        |     |__> Phone
                        |     |__> Fax
                        |     |__> Email
                        |__>  Website 
|__> Three Wheelers
      |__> Manufacturer
            |__> Name of Manufacturer
            |__> Year of make.
            |__> Manuf. Plant.
            |__> Model.
                  |__> Model Name
                        |__> Model Version
                        |__> Model Re-make Version.
                  |__> Year of Release
                  |__> Serial Number  
                  |__> Capacity
                  |     |__> Capacity Type
                  |     |__> Capacity Version
                  |__> Contact Information
                        |__> Company Name
                        |__> Company Address
                        |     |__> Street Address
                        |     |     |__> Lane 1
                        |     |     |__> Lane 2  
                        |     |__> City
                        |     |__> Pin
                        |__> Contact Information
                        |     |__> Phone
                        |     |__> Fax
                        |     |__> Email
                        |__>  Website 
|__>  Four Wheelers
      |__> Manufacturer
            |__> Name of Manufacturer
            |__> Year of make.
            |__> Manuf. Plant.
            |__> Model.
                  |__> Model Name
                        |__> Model Version
                        |__> Model Re-make Version.
                  |__> Year of Release
                  |__> Serial Number  
                  |__> Capacity
                  |     |__> Capacity Type
                  |     |__> Capacity Version
                  |__> Contact Information
                        |__> Company Name
                        |__> Company Address
                        |     |__> Street Address
                        |     |     |__> Lane 1
                        |     |     |__> Lane 2  
                        |     |__> City
                        |     |__> Pin
                        |__> Contact Information
                        |     |__> Phone
                        |     |__> Fax
                        |     |__> Email
                        |__>  Website 

This way the data in the table is tree structured. Also A single manufacturer has all mode of vehicles. I'm in my way to design this DB for use in PgSQL. But for a vast data, data retrieval is a very costly task (Joining all tables).

Can any one guide me of this design, on how the structure looks like. Hard about Inheritance feature of pgSQL. But not aware of how implementation goes.

Thanks in advance.

--
Regards,
----------------------------------------------
Trinath Somanchi,

Re: Postgresql database

From
silly sad
Date:
On 05/14/10 12:42, Trinath Somanchi wrote:
> Hi All,
>
> I have Tree structured database to maintain.
>
> Its as follows.
>
> Vehicles

u r talking about a single entity vehicles
it is very common to represent a single entity with a single table

and IF u also have to have an entity "class of vehicles"
u r free to declare the second table named "class"

and IF u also have to represent a reference of manfacturers, another one 
table u have to create then.

that's trivial case

Re: Postgresql database

From
Trinath Somanchi
Date:
yes... But attribute of this table asl has an instance .. that two year of releases are possible
also... I'm speaking of a very trivial case.

On Fri, May 14, 2010 at 2:44 PM, silly sad <sad@bankir.ru> wrote:
On 05/14/10 12:42, Trinath Somanchi wrote:
Hi All,

I have Tree structured database to maintain.

Its as follows.

Vehicles

u r talking about a single entity vehicles
it is very common to represent a single entity with a single table

and IF u also have to have an entity "class of vehicles"
u r free to declare the second table named "class"

and IF u also have to represent a reference of manfacturers, another one table u have to create then.

that's trivial case

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



--
Regards,
----------------------------------------------
Trinath Somanchi,

Re: Postgresql database

From
Leif Biberg Kristensen
Date:
On Friday 14. May 2010 10.42.17 Trinath Somanchi wrote:
> Hi All,
[snipped]
> This way the data in the table is tree structured. Also A single
> manufacturer has all mode of vehicles. I'm in my way to design this DB for
> use in PgSQL. But for a vast data, data retrieval is a very costly task
> (Joining all tables).
> 
> Can any one guide me of this design, on how the structure looks like. Hard
> about Inheritance feature of pgSQL. But not aware of how implementation
> goes.

You should think about a more normalized structure. Think about a table of 
"manufacturers", and a table of "models", the latter with a foreign key 
referencing the id of the former. The number of wheels is obviously an 
attribute of "models".

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

Re: Postgresql database

From
silly sad
Date:
On 05/14/10 13:28, Trinath Somanchi wrote:
> yes... But attribute of this table asl has an instance .. that two year
> of releases are possible
> also... I'm speaking of a very trivial case.

it doesnt multiplies the multitude of table.
it makes no problems with the "joins of ALL the tables"

Re: Postgresql database

From
"Little, Douglas"
Date:

To do a better job, I’d need to see the data dictionary – eg to understand what capacity means.  Is it the capacity of a model?   Ok, but what about contact info  for a model?  Is this really the contact info of the plant?

But this is what I’d do

 

Mfg – designs a model

plant – builds/contact a plant for model manufacturing

vechicles are built in a plant according to the design of a model

models are of a model type (2,3,4 wheeled vehicles)

 

assuming that contact info is information about the mfg, I’d put the attributes there.

 

 

 

 

 

 

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Trinath Somanchi
Sent: Friday, May 14, 2010 3:42 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Postgresql database

 

Hi All,

 

I have Tree structured database to maintain.

 

Its as follows.

 

Vehicles 

|__> Two Wheelers

      |__> Manufacturer

            |__> Name of Manufacturer

            |__> Year of make.

            |__> Manuf. Plant.

            |__> Model.

                  |__> Model Name

                        |__> Model Version

                        |__> Model Re-make Version.

                  |__> Year of Release

                  |__> Serial Number  

                  |__> Capacity

                  |     |__> Capacity Type

                  |     |__> Capacity Version

                  |__> Contact Information

                        |__> Company Name

                        |__> Company Address

                        |     |__> Street Address

                        |     |     |__> Lane 1

                        |     |     |__> Lane 2  

                        |     |__> City

                        |     |__> Pin

                        |__> Contact Information

                        |     |__> Phone

                        |     |__> Fax

                        |     |__> Email

                        |__>  Website 

|__> Three Wheelers

      |__> Manufacturer

            |__> Name of Manufacturer

            |__> Year of make.

            |__> Manuf. Plant.

            |__> Model.

                  |__> Model Name

                        |__> Model Version

                        |__> Model Re-make Version.

                  |__> Year of Release

                  |__> Serial Number  

                  |__> Capacity

                  |     |__> Capacity Type

                  |     |__> Capacity Version

                  |__> Contact Information

                        |__> Company Name

                        |__> Company Address

                        |     |__> Street Address

                        |     |     |__> Lane 1

                        |     |     |__> Lane 2  

                        |     |__> City

                        |     |__> Pin

                        |__> Contact Information

                        |     |__> Phone

                        |     |__> Fax

                        |     |__> Email

                        |__>  Website 

|__>  Four Wheelers

      |__> Manufacturer

            |__> Name of Manufacturer

            |__> Year of make.

            |__> Manuf. Plant.

            |__> Model.

                  |__> Model Name

                        |__> Model Version

                        |__> Model Re-make Version.

                  |__> Year of Release

                  |__> Serial Number  

                  |__> Capacity

                  |     |__> Capacity Type

                  |     |__> Capacity Version

                  |__> Contact Information

                        |__> Company Name

                        |__> Company Address

                        |     |__> Street Address

                        |     |     |__> Lane 1

                        |     |     |__> Lane 2  

                        |     |__> City

                        |     |__> Pin

                        |__> Contact Information

                        |     |__> Phone

                        |     |__> Fax

                        |     |__> Email

                        |__>  Website 

 

This way the data in the table is tree structured. Also A single manufacturer has all mode of vehicles. I'm in my way to design this DB for use in PgSQL. But for a vast data, data retrieval is a very costly task (Joining all tables).

 

Can any one guide me of this design, on how the structure looks like. Hard about Inheritance feature of pgSQL. But not aware of how implementation goes.

 

Thanks in advance.


--
Regards,
----------------------------------------------
Trinath Somanchi,

Attachment