Thread: understand basics - multiple cars in a database - selection of one

Start by searching on Amazon for Database design.



On 2/4/2014 2:20 PM, avpro avpro wrote:
> hello,
>
> I would like to understand the basics of the database architecture:
> I have several cars in a database;
> when the application is lunched, I would like to select one car and
> then every property, option, transaction applicable to this car should
> be recorded by the application automatically.
>
> basically I'm trying to avoid a menu adding the relevant car
> every-time a property is changed.
>
> how is this happening at database level?
> any references available for this example or something similar?
> thank you.
> John

avpro avpro wrote
> let me write it differently:
> "when the application is lunched, I would like to select one car and then
> every property, option, transaction (applicable to this car) should be
> recorded by the application automatically. "
>
> the property, option, transaction should be recorded by the application
>
> hope this helps. thanks
>
>
> On 4 February 2014 22:25, Richard Broersma <

> richard.broersma@

> >wrote:
>
>> On Tue, Feb 4, 2014 at 1:20 PM, avpro avpro <

> avprowebeden@

> >wrote:
>>
>>
>>> when the application is lunched, I would like to select one car and then
>>> every property, option, transaction applicable to this car should be
>>> recorded by the application automatically.
>>>
>>> basically I'm trying to avoid a menu adding the relevant car every-time
>>> a
>>> property is changed.
>>>
>>
>> Some clarification:  When you select a specific car, what are you
>> expected
>> your application to do?  What does, "this car should be recorded by the
>> application automatically" mean?
>>

entity tables:  cars; properties; options; transactions
relationship tables: cars-properties; cars-options; cars-transactions (this
could just be a foreign-key, probably)

Application:
1) A Drop-Down populated with data in "Cars" and presented to the user
2) User selects a Car
3) Application uses the CarID associated with the selected car to:
3a) Retrieve all cars-properties records having that CarID
3b) Retrieve all cars-options record having that CarID
3c) Retrieve all cars-transactions (or just transactions) having that CarID

Application now has a record of every property/option/transaction associated
with the selected car.

Retrieve basically means issuing "SELECT * FROM
cars-properties|cars-options|cars-transactions WHERE carID = ?"

Now, of course you need some way to actually get data onto those tables.
Fundamentally that means issuing:

"INSERT INTO cars-properties VALUES (carID, propID)"

Though you also need to populate both the cars and properties tables first
so that the relationship table can be assured of having valid data via
foreign key constraints.

If you go and add a property to cars-properties that is not already on
properties you can either abort and ask the user to add it or automatically
add the indicated property first and then add the cars-properties record.

Your request supposes knowledge of many different areas of "application
development" and "application tiers" (e.g., user-interface[UI], middle-ware,
database drivers, PostgreSQL, and database design - to name the most
obvious).  You should not expect much help from volunteers for such a broad
inquiry.  Mailing lists like this one best operate on specific usability
questions.  There are numerous educational materials and institutions
available whose purpose is to teach people how to do this kind of thing -
though I do not personally have any suitable recommendations and you haven't
really described what experience you already possess.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/understand-basics-multiple-cars-in-a-database-selection-of-one-tp5790571p5790595.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

David Johnston wrote
> cars-properties

Note that typically "properties" of an object/entity are modeled as
attributes/columns on the entity table.

cars [ carid serial, car_make text, car_model text, car_year text]

as opposed to separate tables.  That main reason for this is that each
property has a specific data type to which correct values must conform.  A
generic "cars-properties" table would require that all property values be
stored as text.

Note that it is also valid to define cars like:

cars [ carid serial, car_properties hstore|json ]

so that the properties are a simple key-value table.  A combination of both
can work as well.

This is where "Database Design" comes into play and as mentioned many
excellent books exist on the topic.  Once you have a design you then develop
whatever UI elements that are needed to properly interact with it.  While UI
is most important there is ample room for layering and facade-ing that
settling for a known sub-optimal database design will likely be painful in
the long-run.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/understand-basics-multiple-cars-in-a-database-selection-of-one-tp5790571p5790597.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

On Tue, Feb 4, 2014 at 10:34 PM, avpro avpro <avprowebeden@gmail.com> wrote:
> let me write it differently:
> "when the application is lunched, I would like to select one car and then
> every property, option, transaction (applicable to this car) should be
> recorded by the application automatically. "
>
> the property, option, transaction should be recorded by the application
>
> hope this helps. thanks

Oh yes, line feeds definitely made it a lot more understandable!
I suppose what you need here is a good mapper for your application
(e.g., hibernate, but hey, you did not mention your application
language).
I suspect you are asking for a lazy master-child table lookup but hey,
you did not mention your tables.
This could help: http://qt-project.org/doc/qt-4.8/sql-masterdetail.html
but I recommend you getting either a better knowledge of your aim or a
good SQL/relational theory book.

Luca

avpro avpro wrote
> hi all,
>
> thanks for your feedback. I owe you some answers:
> I'm beginner in programming both databases and applications. I'm using
> PostgreSql and Qt / C++ for my application and I'm trying to learn.
> next time I'll come up with a better question. in the end this is a novice
> list. or am I on a wrong list?

Its as good a list as any though don't set your expectations too high.  The
majority of volume on these lists are technical questions about how
PostgreSQL operates.  The ability for any list to answer design philosophy
questions is limited (and of diverse opinions) not to mention the fact that
there are lots of different programming languages that interface with
PostgreSQL and so only a subset of the people monitoring the list are likely
to be using your toolkit - even if we all are using PostgreSQL.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/understand-basics-multiple-cars-in-a-database-selection-of-one-tp5790571p5790901.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.