Need advice on Table Design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need advice on Table Design

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-03 : 16:05:38

My table as follow

tCounter
TrnxID | CoutCd | Desc
-----------------------------
1 | IPH | IPOH
2 | HPT | HPUTRA
3 | JB | JBHARU
4 | KNTN | KUANTAN
*TrnxID is a identity field and primary key
*CoutCd is a unique

tCounterRout
TrnxID | RID | CoutCd | Sequence
----------------------------------------------------
1 | _r00000001 | IPH | 1
2 | _r00000001 | HPT | 2
3 | _r00000001 | JB | 3
4 | _r00000002 | IPH | 1
5 | _r00000002 | KNTN | 2
*TrnxID is a identity field and primary key
*RID,CoutCd is a unique
*RID,Sequence is a unique
*Sequence is a ranking in RID.
Let's say, _r00000001 = IPH - HPT - JB. _r00000002 = IPH - KNTN

Based on tCounterRout, I plan to create Rout value as follow
1. _r00000001, IPH to HPT
2. _r00000001, IPH to JB
3. _r00000001, HPT to JB
4. _r00000002, IPH to KNTN
The rule as follow,
* _r00000001, IPH to HPT is valid because of IPH (1) and HPT (2), if HPT (2) to IPH (1), this is invalid.
* _r00000001, IPH to JB is valid because of IPH (1) and JB (3), if JB (3) to IPH (1), this is invalid.
* _r00000001, HPT (2) to JB (3) is valid. If JB to HPT, this is invalid
* _r00000002, IPH to JB is invalid because in _r00000002, there's no JB

How to design table, i can save RID, From, To, Price
The value as follows
1. In _r00000001, From IPH to HPT the Adult Price is 14.00, Child Price is 7.00
2. In _r00000001, From IPH to JB the Adult Price is 25.00, Child Price is 11.00
3. In _r00000001, From HPT to JB, the Adult Price is 13.00, Child Price is 8.00

Need guidance

mymatrix
Starting Member

24 Posts

Posted - 2010-02-04 : 01:07:36

As per my understanding, you need table design for last part (Pricing of tickets)
Table Design below:

Rates Table
------------
RIDFrom (TrnxID of tCounterRout table)(F.Key datatype - same as of P.Key in tCounterRout table)
RIDTo (TrnxID of tCounterRout table)(F.Key datatype - same as of P.Key in tCounterRout table)
PriceAdult
ChildPrice

Note : No need for PrimaryKey in this table if no further details needs to be stored in child tables.

Secondly, you can store tCounter.TrnxID as F.Key in table tCounterRout in place of CoutCd column.

**************************************

Even my blood group says be -ve to all the negatives.

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-04 : 03:15:34

Your guidance is my inspiration

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources