OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » table design

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

table design [message #10991]

Sat, 28 February 2004 20:27

Sana
Messages: 3
Registered: February 2004

Junior Member

I am trying to design tables for little store dept(where items are issued to employees). Apart from other tables some important tables are as below.But I am till confused whether these are correct or not.. Please guide.....cheers

create table stock_issued
(
emp_id number(3),
item_id number(4),
qty_issued number(3),
month varchar2(15),
issue_date DATE)
);

create table stock_received
(

item_id number(4),
qty_received number(3),
month varchar2(15),
receive_date DATE)
);

create table stock_balance
(
item_id number(4),
balance_cf number(5), --balance carried forward
qty_received number(3),
net_balance number(5),
month varchar2(15),
);

Report message to a moderator

Re: table design [message #10994 is a reply to message #10991]

Sun, 29 February 2004 05:31

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

A numeric "month" column will be easier to work with.

As for whether the definitions are correct or not, perhaps including some (preferably inline) constraints would help clarify things. For example:

SQL> CREATE TABLE departments
  2  ( dep_id NUMBER(6) CONSTRAINT dep_pk PRIMARY KEY
  3  , dep_name VARCHAR2(40) CONSTRAINT dep_name_uk UNIQUE )     
  4  ORGANIZATION INDEX;

Table created.

SQL> CREATE TABLE employees
  2  ( emp_id NUMBER(6) CONSTRAINT emp_pk PRIMARY KEY
  3  , emp_dep_id CONSTRAINT emp_dep_fk REFERENCES departments
  4  , emp_name VARCHAR2(50) )
  5  ORGANIZATION INDEX;

Table created.

SQL> @constr departments

Type     Constraint name                Definition                                                   Status
-------- ------------------------------ ------------------------------------------------------------ --------
Primary  DEP_PK                         Primary key (DEP_ID)                                         ENABLED
Unique   DEP_NAME_UK                    Unique key (DEP_NAME)                                        ENABLED

Tables referencing DEPARTMENTS:
Table name                     Constraint name                Status   Deferrable?    Deferred?
------------------------------ ------------------------------ -------- -------------- ---------
EMPLOYEES                      EMP_DEP_FK                     ENABLED  NOT DEFERRABLE IMMEDIATE

SQL> @constr employees

Type     Constraint name                Definition                                                   Status
-------- ------------------------------ ------------------------------------------------------------ --------
Primary  EMP_PK                         Primary key (EMP_ID)                                         ENABLED
FK       EMP_DEP_FK                     Foreign key (EMP_DEP_ID) to DEPARTMENTS (DEP_PK)             ENABLED

Report message to a moderator

Re: table design [message #11003 is a reply to message #10994]

Sun, 29 February 2004 20:05

Sana
Messages: 3
Registered: February 2004

Junior Member

Thanks for valuable help. But what I really wanted to know is how to design such tables that will handle the situation below.

If an item is issued to a person, where should its quantity be placed(i mean in which table), which table should containt quantity in stock and where should we put balance of item quantity. Thanks for giving time..
cheers

Report message to a moderator

Previous Topic:	Create trigger on table that updates and appends
Next Topic:	error on called report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 18:55:57 CDT 2024