SQL Server 2008: can 2 tables have the same composite primary key? -


this list of tables im using in hotel reservation uni project

in case, tables reserve_details , payment_details; can 2 tables have same composite primary key (clientid, roomid)?

or should merge 2 tables become one:

clientid[pk], roomid[pk], reserveid[fk], paymentid[fk]

in case, tables reserve_details , payment_details; can 2 tables have same composite primary key (clientid, roomid) ?

yes, can, happens in relational databases.

  • (you have not set tag, since (a) using sql server, , (b) have compound keys, indicates movement in direction of relational database, making assumption.)

whether should or not, in particular instance, separate matter. , gets design; modelling; normalisation.

or should merge 2 tables become one:
clientid[pk], roomid[pk], reserveid[fk], paymentid[fk] ?

ok, realise design not robust.

that normalisation question. cannot answered on just pair of tables, because:

  • normalisation overall issue, tables need taken account, together, in 1 exercise.

  • that exercise determines keys. pks change, fks in child tables change.

  • the structure have detailed record filing system, not set of relational tables. full of duplication, , confusion (facts1 not defined).

  • you appear making classic mistake of stamping id field on every file. (a) cripples modelling exercise (hence difficulties experiencing) , (b) guarantees rfs instead of rdb.

solution

first, let me level of detail in answer constrained level of detail given in question. in case, since have provided great detail, able make reasonable decisions data.

if may, easier correct entire lot of them, discuss , correct 1 or other pair of files.

  • various files need normalised ("merged" or separated)

  • various duplicates fields need normalised (located relevant facts, such duplication eliminated)

  • various facts1 need clarified , established properly.

please consider this:

  • reservation trd

    • that idef1x model, rendered @ table-relation level. idef1x standard modelling relational databases. please advised every little tick; notch; , mark; crows feet; solid vs dashed lines; square vs round corners; means specific , important. refer idef1x notation. if not understand notation, not able understand or work model.

  • the predicates important, have given them you.

    • if information on important relational concept of predicates, , how used both understand , verify model, describe in business terms, visit this answer, scroll down (way down) until find predicate section, , read carefully.

assumption

i have made following assumptions:

  • given 2015, when reserving room, hotel requires credit card details. forms basis reservation.

  • rooms exist independently. roomid silly, given rooms uniquely identified roomno. pk ( roomno ).

  • clients exist independently.

    • the real identifier has (namelast, namefirst, initial ... ), plus possibly statecode. otherwise have duplicate rows not permitted in relational database.

    • however, key wide migrated child tables 2, add 3 surrogate ( clientid ), make pk, , demote real identifier ak.

  • creditcards belong clients, , want them identified once (not on each transaction). pk ( clientid, creditcardno ).

  • reservations rooms, not exist in isolation, independently. therefore reservation child of room, , pk ( roomno, date ). can use datetime if rooms not full days, if short meetings, liaisons, etc.

  • a reservation may, or may not, progress filled. pk identical parent. allows 1 filled reservation per reservation.

  • payments not exist in isolation either. payments reservations.

    • the payment may reservationfee (for "no shows"), or filled reservation, plus extras. leave work out duration changes; etc. multiple payments (against reservation) supported.

    • the pk identifier of parent, reservation, plus sequence number: ( roomno, date, sequenceno ).

relational database

you have relational database, levels of (a) integrity (b) power , (c) speed, each of way, way, beyond capabilities of record filing system. notice, there 1 id column.

note

  1. a database collection of facts real world, limited scope app engages.

  2. which single reason justifies use of surrogate.

  3. a surrogate addition, not substitution. real keys make row unique cannot abandoned.

please feel free ask questions or comment.


Comments

Post a Comment

Popular posts from this blog

asp.net mvc - SSO between MVCForum and Umbraco7 -

i need integrate sso between mvcforum (forum of company) , umbraco7 (manages cms company). however struggling shared authentication work. tried several ways suggested on other threads still not running expected. here did: 1. in mvcforum (forum.mywebsite.com) 1.1. machine key in web.xml: <machinekey validationkey="the same key umbraco7" decryptionkey="the same key umbraco7" validation="sha1" decryption="aes" /> 1.2. authenticate forms in web.xml <forms name="my-sso-auth" protection="all" path="/" timeout="43200" domain="forum.mywebsite.com" loginurl="/members/logon" enablecrossappredirects="true" /> 2. in umbraco7 (news.mywebsite.com) 2.1. machine key in web.xml: <machinekey validationkey="the same key mvcforum" decryptionkey="the same key mvcforum" validation="sha1" decryption="aes" /> 2.2. a
Read more

Python Tkinter keyboard using bind -

i have problem using bind option can click button in keyboard, , call function once key pressed. tried taking other codes have similar purpose , saw i'm doing pretty same, though still have problem must've missed. thankful if me this. here's code: # -*- coding: utf-8 -*- #imports tkinter import * pil import imagetk, image import time import tkmessagebox #===========================================================================================================================================# #tkinter class class app(frame): def __init__(self, master=none): frame.__init__(self, master) self.pack() #===========================================================================================================================================# #game pieces classes class board: def __init__(self, rows = 7, columns = 6, picture_height = none, picture_width = none): self.rows = rows self.columns = columns self.picture
Read more

ubuntu - Selenium Node Not Connecting to Hub, Not Opening Port -

i'm trying set demo selenium grid. have digital ocean ubuntu 14.02 droplet. (i have 2 , tried set using 2 different machines got nowhere.) i'm trying run hub , node both locally. here input both hub, node, , nmap results. https://gist.github.com/mekhami/d5bbecca5ff0de9d2dc9 so, node starting tcp connection reset , port it's supposed operate on closed. i have had no luck in number of different options , configurations work. but... of course, worked randomly once. don't know why, don't know did, know 1 brief moment, node connected , saw on grid console. tried run test , went down again , i've never seen come up. please let me know more information can provide. update: randomly came online again. don't know happened, might related timeout or time_wait or going on. this because vm/server not have enough entropy available. to resolve problem, install haveged (or other similar things). the instructions of how install haveged on ubu
Read more