Home » Developer & Programmer » Designer » Database Design Problem
Database Design Problem [message #10297] Tue, 13 January 2004 21:35 Go to next message
CheekuK
Messages: 14
Registered: December 2003
Junior Member
Hi Everybody,
I am stuck in a design problem a little bit. I have a newspapaer agency which has some publishings and subscribtions. A subscriber can subscribe to multiple multiple publications.
I created a table Subcriber
SID PK,
Sname,
OtherDetails
Subdetailid
I have another table Subscriberdetail which has
SubDetailId PK
PID PK
DAteofsubscription
Dateofcancellation
Status

Now I want that Subscriber, subscriberdetail should have a parent child relationship on Subdetailid. But I am not able to create a constarint.
Any help wud be appreciated.
Thanks and Regards
Re: Database Design Problem [message #10298 is a reply to message #10297] Tue, 13 January 2004 22:44 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
How about:
CREATE TABLE subscribers
( subscriber_id    NUMBER CONSTRAINT sub_pk PRIMARY KEY
, name             VARCHAR2(40) NOT NULL )
ORGANIZATION INDEX
MONITORING;

CREATE TABLE subscriptions
( subscription_id  NUMBER CONSTRAINT sus_pk PRIMARY KEY
, subscriber_id    CONSTRAINT sus_sub_fk REFERENCES subscribers
, start_date       DATE NOT NULL
, end_date         DATE
, status           VARCHAR2(1)  <i>-- Maybe this should be FK to a 'Subscription Statuses' table?</i>
, CONSTRAINT sus_status_chk CHECK (status IN ('A','B','C') )
, CONSTRAINT sus_date_chk CHECK (end_date >= start_date ) )
ORGANIZATION INDEX
MONITORING;
Re: Database Design Problem [message #10299 is a reply to message #10298] Tue, 13 January 2004 22:53 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
...now I think about it, a PUBLICATIONS table might help:
CREATE TABLE subscribers
( subscriber_id    NUMBER CONSTRAINT sub_pk PRIMARY KEY
, name             VARCHAR2(40) NOT NULL )
ORGANIZATION INDEX
MONITORING;

CREATE TABLE publications
( publication_id   NUMBER CONSTRAINT pub_pk PRIMARY KEY
, name             VARCHAR2(40) NOT NULL CONSTRAINT pub_name_uk UNIQUE )
ORGANIZATION INDEX
MONITORING;

CREATE TABLE subscriptions
( subscription_id  NUMBER CONSTRAINT sus_pk PRIMARY KEY
, subscriber_id    CONSTRAINT sus_sub_fk REFERENCES subscribers
, publication_id   CONSTRAINT sus_pub_fk REFERENCES publications
, start_date       DATE NOT NULL
, end_date         DATE
, status           VARCHAR2(1)
, CONSTRAINT sus_pub_uk UNIQUE (subscriber_id, publication_id, start_date)
, CONSTRAINT sus_status_chk CHECK (status IN ('A','B','C') )
, CONSTRAINT sus_date_chk CHECK (end_date >= start_date ) )
ORGANIZATION INDEX
MONITORING;
Re: Database Design Problem [message #10300 is a reply to message #10299] Wed, 14 January 2004 00:48 Go to previous messageGo to next message
CheekuK
Messages: 14
Registered: December 2003
Junior Member
Hi William,
Thanks for the efforts you took with this..This one seems really good. Can you just explain me what is this Organization Index Monitoring you have used...??
Regards
Re: Database Design Problem [message #10313 is a reply to message #10300] Wed, 14 January 2004 08:46 Go to previous message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
ORGANIZATION INDEX makes it an index-organized table (IOT), in which the table's primary key index is combined with the data blocks. This saves the storage overhead of creating a separate index and makes primary key index lookups faster. There are some minor downsides (e.g. you can't disable the constraint for a bulk load, they limit what you can do with partitioning and bitmap indexes, in theory non-pk index lookups are less efficient though I've yet to see much difference in practice, etc) but mostly they are just easier and better.

MONITORING makes it easier to automate collecting stats on the table (i.e. analyzing it). The 'GATHER AUTO' option of DBMS_STATS.GATHER_SCHEMA_STATS tells it to analyze only those tables whose data has changed significantly since they were last analyzed.

These options aren't mandatory, I just tend to use them unless there is a reason not to.
Previous Topic: Normalization & De-Normalization
Next Topic: 'partial' foreign keys ??
Goto Forum:
  


Current Time: Tue Mar 19 03:43:29 CDT 2024