Re: [sqlite] database development - correct way?

[Tim Romano]

Oliver,
Your first solution

CREATE TABLE customer(
  idINTEGER PRIMARY KEY AUTOINCREMENT,
  customernumberINTEGER,
  customeroriginINTEGER,
  name  TEXT,
  UNIQUE(customernumber,customerorigin)
  );

is the better of the two because it simplifies foreign keys: the OrderHeader
table would contain a single-column reference to CUSTOMER rather than two
columns.  Either approach is legitimate as far as RDBMS design is concerned;
however some client-side application frameworks and middleware libraries do
not support multi-column primary keys.

Regards
Tim Romano
Swarthmore PA



On Wed, Jun 9, 2010 at 1:37 PM, Oliver Peters  wrote:

> Rich Shepard  writes:
>
> >
> > On Wed, 9 Jun 2010, Oliver Peters wrote:
> >
> > > So I assume that it is not(!) a mistake not(!) to use a composite PK in
> my
> > > table "customer" (customernumber,customerorigin) and to refer to it
> from
> > > the table "order" where I had to use these fields as a composite FK?
> >
> > Oliver,
> >
> >Too many negatives there for me to really follow what you're asking.
>
>
> sorry, I try my very best:
>
> adverted to the table customer I've 2 possible solutions and I ask myself
> if the
> first one is incorrect - the reason why I ask lies in the behaviour of my
> frontend (OpenOffice Base) that has problems to handle UNIQUE-Constraints
> under
> special circumstances (main-subform-connections)
>
> solution 1
> --
> CREATE TABLE customer(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   customernumberINTEGER,
>   customeroriginINTEGER,
>   name  TEXT,
>   UNIQUE(customernumber,customerorigin)
>   );
>
>
> solution 2
> --
> CREATE TABLE customer(
>customernumberINTEGER,
>   customeroriginINTEGER,
>   name  TEXT,
>PRIMARY KEY(customernumber,customerorigin)
>   );
>
> thx for your patience
> Oliver
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database development - correct way?

[Oliver Peters]

Rich Shepard  writes:

> 
> On Wed, 9 Jun 2010, Oliver Peters wrote:
> 
> > So I assume that it is not(!) a mistake not(!) to use a composite PK in my
> > table "customer" (customernumber,customerorigin) and to refer to it from
> > the table "order" where I had to use these fields as a composite FK?
> 
> Oliver,
> 
>Too many negatives there for me to really follow what you're asking.


sorry, I try my very best:

adverted to the table customer I've 2 possible solutions and I ask myself if the
first one is incorrect - the reason why I ask lies in the behaviour of my
frontend (OpenOffice Base) that has problems to handle UNIQUE-Constraints under
special circumstances (main-subform-connections)

solution 1
--
CREATE TABLE customer(
   idINTEGER PRIMARY KEY AUTOINCREMENT,
   customernumberINTEGER,
   customeroriginINTEGER,
   name  TEXT,
   UNIQUE(customernumber,customerorigin)
   );


solution 2
--
CREATE TABLE customer(
   customernumberINTEGER,
   customeroriginINTEGER,
   name  TEXT,
   PRIMARY KEY(customernumber,customerorigin)
   );

thx for your patience
Oliver

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database development - correct way?

[Rich Shepard]

On Wed, 9 Jun 2010, Oliver Peters wrote:

> So I assume that it is not(!) a mistake not(!) to use a composite PK in my
> table "customer" (customernumber,customerorigin) and to refer to it from
> the table "order" where I had to use these fields as a composite FK?

Oliver,

   Too many negatives there for me to really follow what you're asking.

   Whenever possible, the primary key for a table should be a 'natural' value
that uniquely describes that entity. For example, a vehicle identification
number, license registration number, or a person's passport number. For a
customer table there is no natural identifier so you make one up: the ID
column. This could be a sequential number or a compisite based on the
customer's name. The customer table stands alone and can be used in various
applications so you want only the single ID attribute as the primary key.

   If it is possible for a specific named customer to have several origins,
then you would want a composite primary key. But, if each customer has only
a single origin then you should have a simple primary key, the customer ID.

   The order table should have its own ID column as a primary key. This way
you assign each new order a different primary key even if the same customer
places two or more orders on the same date. For example:

order_numbercustomer_id date
1   1   9 June 2010
2   3   9 June 2010
3   1   9 June 2010

This makes each order unique regardless of customer, date, or items ordered.

HTH,

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database development - correct way?

[Rich Shepard]

On Wed, 9 Jun 2010, Tim Romano wrote:

> Typically, Orders are divided into OrderHeader and OrderDetail tables:
>
> OrderHeader
> id integer primary key
> orderdate
> customerid
>
> OrderDetail
> id
> orderid  references OrderHeader(id)
> articleid references article(id)
> quantity int
>
> And you could then place a unique composite index on (orderid, articleid) in
> OrderDetail if you wanted to prevent the same article from appearing on more
> than one line-item of the order.

   To generalize this, when designing a database schema it is best to look at
the relationships involved. For example, the customer-order relationship is
1-to-many (each customer may have many separate orders). However, orders and
items are a many-to-many relationship (each order can have many items, and
each item can be on many orders).

   Usually, many-to-many relationships require an intermediate table that
uses the primary keys from both tables as a composite primary key.

   And, as Tim wrote, separating the order header from the line item details
makes both creation and maintenance much easier.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database development - correct way?

[Oliver Peters]

Adam DeVita  writes:

> 
> I wouldn't advise using an SQL keyword as a table name: "Order"
> 
> I presume that your order collection table example is shorter than the real
> one for the sake of the example?

[...]

yes - and the content has nothing to do with my real tables. The example should
be only a well known for experienced db-designers I assume (I'm not an
experienced db-designer ;-) ).

So I assume that it is not(!) a mistake not(!) to use a composite PK in my table
"customer" (customernumber,customerorigin) and to refer to it from the table
"order" where I had to use these fields as a composite FK?

greetings
Oliver




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database development - correct way?

[Tim Romano]

Placing a unique composite index on (customer, article) in the Orders table
prevents recurring purchases of the same article by the same customer.  Acme
might buy a widget in June and then place another order for a widget in
September, but the order would be rejected as a duplicate.

Typically, Orders are divided into OrderHeader and OrderDetail tables:

OrderHeader
id integer primary key
orderdate
customerid

OrderDetail
id
orderid  references OrderHeader(id)
articleid references article(id)
quantity int

And you could then place a unique composite index on (orderid, articleid) in
OrderDetail if you wanted to prevent the same article from appearing on more
than one line-item of the order.

Regards
Tim Romano
Swarthmore PA



Regards
Tim Romano
.


On Wed, Jun 9, 2010 at 9:09 AM, Oliver Peters  wrote:

> Hello,
>
> despite it's just a question about construction I hope somebody is willing
> to
> push me into the right direction if necessary.
>
> my simplified case
> --
> I've the 3 tables customer, article and order
>
> my thoughts about the table customer:
> the customernumber can be from 3 different sources with possible
> overlappings
> (i.e. I can get 3 from source A and 3 from source B) so I adopt the
> field customerorigin to make a difference
> For simplicity I created a field id that is taking the part of the Primary
> Key
> and just declared "UNIQUE(customernumber,customerorigin)"
>
>
> the SQL-Code
> 
> CREATE TABLE customer(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   customernumberINTEGER,
>   customeroriginINTEGER,
>   name  TEXT,
>   UNIQUE(customernumber,customerorigin)
>   );
>
> CREATE TABLE article(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   name  TEXT
>   );
>
> CREATE TABLE order(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   id_customer   INTEGER,
>   id_articleINTEGER,
>   UNIQUE(id_customer,id_article),
>   FOREIGN KEY(id_customer) REFERENCES customer(id),
>   FOREIGN KEY(id_article)  REFERENCES article(id)
>   );
>
>
> simple question
> ---
> Is this a correct way or do I make a mistake?
>
> greetings
> Oliver
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database development - correct way?

[Adam DeVita]

I wouldn't advise using an SQL keyword as a table name: "Order"

I presume that your order collection table example is shorter than the real
one for the sake of the example?  One often sees a date or time of some sort
associated with an order so that one can create reports based on dates.
(How many sales did we make this month?)

regards,
Adam




On Wed, Jun 9, 2010 at 9:09 AM, Oliver Peters  wrote:

> Hello,
>
> despite it's just a question about construction I hope somebody is willing
> to
> push me into the right direction if necessary.
>
> my simplified case
> --
> I've the 3 tables customer, article and order
>
> my thoughts about the table customer:
> the customernumber can be from 3 different sources with possible
> overlappings
> (i.e. I can get 3 from source A and 3 from source B) so I adopt the
> field customerorigin to make a difference
> For simplicity I created a field id that is taking the part of the Primary
> Key
> and just declared "UNIQUE(customernumber,customerorigin)"
>
>
> the SQL-Code
> 
> CREATE TABLE customer(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   customernumberINTEGER,
>   customeroriginINTEGER,
>   name  TEXT,
>   UNIQUE(customernumber,customerorigin)
>   );
>
> CREATE TABLE article(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   name  TEXT
>   );
>
> CREATE TABLE order(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   id_customer   INTEGER,
>   id_articleINTEGER,
>   UNIQUE(id_customer,id_article),
>   FOREIGN KEY(id_customer) REFERENCES customer(id),
>   FOREIGN KEY(id_article)  REFERENCES article(id)
>   );
>
>
> simple question
> ---
> Is this a correct way or do I make a mistake?
>
> greetings
> Oliver
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database development - correct way?

[Oliver Peters]

Hello,

despite it's just a question about construction I hope somebody is willing to
push me into the right direction if necessary.

my simplified case
--
I've the 3 tables customer, article and order

my thoughts about the table customer:
the customernumber can be from 3 different sources with possible overlappings
(i.e. I can get 3 from source A and 3 from source B) so I adopt the
field customerorigin to make a difference
For simplicity I created a field id that is taking the part of the Primary Key
and just declared "UNIQUE(customernumber,customerorigin)"


the SQL-Code

CREATE TABLE customer(
   idINTEGER PRIMARY KEY AUTOINCREMENT,
   customernumberINTEGER,
   customeroriginINTEGER,
   name  TEXT,
   UNIQUE(customernumber,customerorigin)
   );

CREATE TABLE article(
   idINTEGER PRIMARY KEY AUTOINCREMENT,
   name  TEXT
   );

CREATE TABLE order(
   idINTEGER PRIMARY KEY AUTOINCREMENT,
   id_customer   INTEGER,
   id_articleINTEGER,
   UNIQUE(id_customer,id_article),
   FOREIGN KEY(id_customer) REFERENCES customer(id),
   FOREIGN KEY(id_article)  REFERENCES article(id)
   );


simple question
---
Is this a correct way or do I make a mistake?

greetings
Oliver

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users