Styjun

Have I saved too much for retirement so far?

Why isn't KTEX's runway designation 10/28 instead of 9/27?

Teaching indefinite integrals that require special-casing

What (else) happened July 1st 1858 in London?

Can I Retrieve Email Addresses from BCC?

Hostile work environment after whistle-blowing on coworker and our boss. What do I do?

What would you call a finite collection of unordered objects that are not necessarily distinct?

What will be the benefits of Brexit?

Pronouncing Homer as in modern Greek

How can I successfully establish a nationwide combat training program for a large country?

How can I raise concerns with a new DM about XP splitting?

Is it possible to have a strip of cold climate in the middle of a planet?

Organic chemistry Iodoform Reaction

Why are on-board computers allowed to change controls without notifying the pilots?

Simple image editor tool to draw a simple box/rectangle in an existing image

Did US corporations pay demonstrators in the German demonstrations against article 13?

Fast sudoku solver

What is the opposite of 'gravitas'?

What does the "3am" section means in manpages?

What is the smallest body in which a sling shot maneuver can be performed?

How do ultrasonic sensors differentiate between transmitted and received signals?

Is there enough fresh water in the world to eradicate the drinking water crisis?

Can one define wavefronts for waves travelling on a stretched string?

Are Warlocks Arcane or Divine?

'Nested' foreign key relationship

How can foreign key constraints be temporarily disabled using T-SQL?What's the best practice for primary keys in tables?How can I list all foreign keys referencing a given table in SQL Server?foreign key and primary key errorAdd Foreign Key relationship between two DatabasesMySQL whats wrong with my foreign keys?Foreign key constraints: When to use ON UPDATE and ON DELETESQL How to Set Foreign Keyspostgresql foreign key syntaxPrimary Key and Foreign key in same table

0

A Product has a code (and other attributes). A Product is available in various countries, with a different name in each. A Packaged Product is a product in a particular pack size.

An invoice detail line will hold a package_code and a country_code. The permissible countries for a PackagedProduct are restricted by the contents of the ProductCountry table, i.e. the PackagedProduct is only available in a given Country if the Product is available in that Country.

Here's my approach (Country and InvoiceDetail tables omitted):

create table Product (
 product_code varchar(15),
 strength varchar(10),
 -- other Product attributes
 constraint pk_Product primary key (product_code)
 );

create table ProductCountry (
 product_code varchar(15),
 country_code char(2),
 local_name varchar(100),
 constraint pk_ProductCountry primary key (product_code, country_code),
 foreign key (product_code) references Product(product_code),
 foreign key (country_code) references Country(country_code)
 );

create table PackagedProduct (
 package_code varchar(20),
 product_code varchar(15),
 pack_size int,
 constraint pk_PackagedProduct primary key (package_code),
 foreign key (product_code) references Product(product_code)
 );

My problem is that this design does not restrict Invoice Detail records to valid combinations of PackagedProduct and Country.

Can we improve this design?

sql tsql relational-database

share|improve this question

asked Mar 21 at 14:40

Rik BradleyRik Bradley

12

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You may want to look at CHECK constraints and triggers for ways of performing more complex data validation. Ponder carefully what update and delete statements should do, e.g. if a product is no longer offered in Elbonia what becomes of InvoiceDetails that reflect shipments to the muddy land.
  
  – HABO
  Mar 21 at 20:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is a faq. You can constrain this declaratively via compound PKs & FKs that include product & country.
  
  – philipxy
  Mar 22 at 6:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @HABO yes I feared that I may have to resort to manual constraints. I think the Elbonia invoice remains valid - we just won't make any more in future!
  
  – Rik Bradley
  Mar 22 at 9:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @philipxy by compound FK's, do you mean FKs referencing more than one column? I don't see how that addresses the problem, would you care to spell it out for me using my example?
  
  – Rik Bradley
  Mar 22 at 9:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  "compound/composite key" is a google away. I don't have time or my faq list now. Meanwhile from my comments list: Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. PS The idea is to have InvoiceDetail PK (invoice, product, country) & FK (product, country) to ProductCountry. (PK (product) to Product is then implied.)
  
  – philipxy
  Mar 22 at 19:21
  

  
  
  
  

 | 
show 2 more comments

0

A Product has a code (and other attributes). A Product is available in various countries, with a different name in each. A Packaged Product is a product in a particular pack size.

An invoice detail line will hold a package_code and a country_code. The permissible countries for a PackagedProduct are restricted by the contents of the ProductCountry table, i.e. the PackagedProduct is only available in a given Country if the Product is available in that Country.

Here's my approach (Country and InvoiceDetail tables omitted):

create table Product (
 product_code varchar(15),
 strength varchar(10),
 -- other Product attributes
 constraint pk_Product primary key (product_code)
 );

create table ProductCountry (
 product_code varchar(15),
 country_code char(2),
 local_name varchar(100),
 constraint pk_ProductCountry primary key (product_code, country_code),
 foreign key (product_code) references Product(product_code),
 foreign key (country_code) references Country(country_code)
 );

create table PackagedProduct (
 package_code varchar(20),
 product_code varchar(15),
 pack_size int,
 constraint pk_PackagedProduct primary key (package_code),
 foreign key (product_code) references Product(product_code)
 );

My problem is that this design does not restrict Invoice Detail records to valid combinations of PackagedProduct and Country.

Can we improve this design?

sql tsql relational-database

share|improve this question

asked Mar 21 at 14:40

Rik BradleyRik Bradley

12

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You may want to look at CHECK constraints and triggers for ways of performing more complex data validation. Ponder carefully what update and delete statements should do, e.g. if a product is no longer offered in Elbonia what becomes of InvoiceDetails that reflect shipments to the muddy land.
  
  – HABO
  Mar 21 at 20:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is a faq. You can constrain this declaratively via compound PKs & FKs that include product & country.
  
  – philipxy
  Mar 22 at 6:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @HABO yes I feared that I may have to resort to manual constraints. I think the Elbonia invoice remains valid - we just won't make any more in future!
  
  – Rik Bradley
  Mar 22 at 9:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @philipxy by compound FK's, do you mean FKs referencing more than one column? I don't see how that addresses the problem, would you care to spell it out for me using my example?
  
  – Rik Bradley
  Mar 22 at 9:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  "compound/composite key" is a google away. I don't have time or my faq list now. Meanwhile from my comments list: Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. PS The idea is to have InvoiceDetail PK (invoice, product, country) & FK (product, country) to ProductCountry. (PK (product) to Product is then implied.)
  
  – philipxy
  Mar 22 at 19:21
  

  
  
  
  

 | 
show 2 more comments

A Product has a code (and other attributes). A Product is available in various countries, with a different name in each. A Packaged Product is a product in a particular pack size.

An invoice detail line will hold a package_code and a country_code. The permissible countries for a PackagedProduct are restricted by the contents of the ProductCountry table, i.e. the PackagedProduct is only available in a given Country if the Product is available in that Country.

Here's my approach (Country and InvoiceDetail tables omitted):

create table Product (
 product_code varchar(15),
 strength varchar(10),
 -- other Product attributes
 constraint pk_Product primary key (product_code)
 );

create table ProductCountry (
 product_code varchar(15),
 country_code char(2),
 local_name varchar(100),
 constraint pk_ProductCountry primary key (product_code, country_code),
 foreign key (product_code) references Product(product_code),
 foreign key (country_code) references Country(country_code)
 );

create table PackagedProduct (
 package_code varchar(20),
 product_code varchar(15),
 pack_size int,
 constraint pk_PackagedProduct primary key (package_code),
 foreign key (product_code) references Product(product_code)
 );

My problem is that this design does not restrict Invoice Detail records to valid combinations of PackagedProduct and Country.

Can we improve this design?

sql tsql relational-database

share|improve this question

asked Mar 21 at 14:40

Rik BradleyRik Bradley

12

create table Product (
 product_code varchar(15),
 strength varchar(10),
 -- other Product attributes
 constraint pk_Product primary key (product_code)
 );

create table ProductCountry (
 product_code varchar(15),
 country_code char(2),
 local_name varchar(100),
 constraint pk_ProductCountry primary key (product_code, country_code),
 foreign key (product_code) references Product(product_code),
 foreign key (country_code) references Country(country_code)
 );

create table PackagedProduct (
 package_code varchar(20),
 product_code varchar(15),
 pack_size int,
 constraint pk_PackagedProduct primary key (package_code),
 foreign key (product_code) references Product(product_code)
 );

share|improve this question

asked Mar 21 at 14:40

Rik BradleyRik Bradley

12

share|improve this question

asked Mar 21 at 14:40

Rik BradleyRik Bradley

12

asked Mar 21 at 14:40

Rik BradleyRik Bradley

12

asked Mar 21 at 14:40

Rik BradleyRik Bradley

12