Database primary key composed to foreign key

Question

0.00/5 (No votes)

See more:

Hello everyone,

I have a table named minibar_bill and i use it for keeping evidence of client's expenditure. I'm trying to build a hotel/pension system management.

I thought that i could make a table

Minibar_bill with (id_bill, id_minibar_product, id_client)

And i would like to add those info on an invoice based on bill_id...

How should i do it ?

I mean i want to have something like that:

Id_bill(1)
id_minibar_product(1,2,3)
id_client(123)

Where id_bill(1) - would be the first bill record in database
id_minibar_product(1,2,3) - would be product 1,2,3 which has been consumed by client
id_client(123) - client id which we use on invoice to collect data from Client table in order to print them on invoice( i will use C# for UI ).

What I have tried:

I've tried to make a db with field id_bill and id_product but i think it's a wrong approach since i made them a composed primary key and i cannot add them to foreign key in Invoice table.

Posted 7-Jun-17 22:21pm

serbanov

Updated 7-Jun-17 23:23pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2017-06-07T23:23:00

Solution 1

Based on your description, you need at least three tables to begin with, e.g.

Table: bill
bill_id (primary key)
bill_table_no
bill_datetime
etc

Table: product
product_id (primary key)
product_price
etc

Table: bill_product (both bill_id and product_id are composite primary key)
bill_id (foreign key to bill table)
product_id (foreign key to product table)
qty
etc

Learn more Introduction to database design[^]

Posted 7-Jun-17 23:23pm

Peter Leow

Updated 7-Jun-17 23:24pm

v2

Comments

serbanov 8-Jun-17 5:36am

The problem is that i do not find a solution to have a better design ... even if i post my table names, they are in romanian language ... and you will not understand very much ...

The problem i face is that i have the following tables Invoice,Client, Room, Minibar, Minibar_bill and Minibar_products.

What i wish for is to link Invoice to minibar_bill in order to have the status on a single Invoice of all products from minibar which have been bought by a customer...

Peter Leow 8-Jun-17 5:42am

Read the links provided, then figure out the relationships between the various tables (entities), if need be, re-design your tables. It is a trial and error process, it will only stop when the design fits your requirements.