[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,
Re: [SQL] Postgresql database
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
Re: [SQL] Postgresql database
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 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: [SQL] Postgresql database
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/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Postgresql database
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" -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Postgresql database
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 [cid:image002.png@01CAF33E.5FD30FB0] 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-ow...@postgresql.org [mailto:pgsql-sql-ow...@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, <>
