1:m (1 Viewer)

S

salim

New member
Local time
Today, 03:57
Joined
Mar 18, 2008
Messages
9
Hi again,
I think my problem is so complex, I have been working on it and I came up with some solutions, now my question is as follow:
I try to establish a 1:m between two entities or table ( vehicle_tbl and Booking_tbl) but I cannot, i get the error message of not finding the index key, anyway here are the tables, I checked all the attirbutes and matching data to make sure there is no data missing


vehicle_type_tbl ( vehicle_type PK...ect all the rest of attributes)
vehicle_tbl ( vehicle_reg PK, vehicle_type FK, ....ect...attributes)
Booking_tbl (Booking_id PK, vehicle_reg FK, Customer_id FK...ect...all the rest of tributes)


would that be because the vehicle_tbl has a compound keys ? and has already a relationship with 1:m end with vehicle_Typetbl? that's why i cannot establish 1:m with Booking_tbl ? if it's the case then how would I mark the vehicle_type ?
 
G

georgedwilkinson

AWF VIP
Local time
Yesterday, 21:57
Joined
Mar 4, 2008
Messages
3,856
Why does it have a compound key? Give it an artificial/surrogate key and see if that solves your problem. If so, make your current compound key into a unique constraint.

If that doesn't help, can you post your db?
 
S

salim

New member
Local time
Today, 03:57
Joined
Mar 18, 2008
Messages
9
i don't think I need artificial or surrogate key as the one I have is ok, but again the question o the problem remains that while I have the vehicle_type PK inb the other table it's automatically a FK which will becomea PK in the design, is that correct? remind me if I am not plz, how do you want me to post the db?
 
G

georgedwilkinson

AWF VIP
Local time
Yesterday, 21:57
Joined
Mar 4, 2008
Messages
3,856
No, that is not correct.

Read up on primary and foreign keys and how to make them.
 
Rabbie

Rabbie

Super Moderator
Local time
Today, 03:57
Joined
Jul 10, 2007
Messages
5,906
If the PK of one table is going to be used as a FK in another table then it is much more efficient to use a single Autonumber field as the PK and define the FK field as Long. This means your indexes will be efficient and not too bulky.
 
S

salim

New member
Local time
Today, 03:57
Joined
Mar 18, 2008
Messages
9
how about if I don't use autonumber? again with this rule I cannot use it because a Vehicle Registration Number is unique and cannot change, in the company we have a set of Reg and cannot nver be auto.
the problem still is there, I cannot solve it, where is it, some said that I need to go and learn how to set PK and FK, well I will do now, but meanwhile can anyone think of anything?
thanks
 
Rabbie

Rabbie

Super Moderator
Local time
Today, 03:57
Joined
Jul 10, 2007
Messages
5,906
Salim, It is much more efficient to use a long or integer for a PK and not a text field. You can have an autonumber and the VRN in the same record. If The VRN is a pure integer with no alpha then use that by all means.

Its your DB so you do what you want. We are only trying to give you helpful advice and a solution since you asked. A compound key will give you a huge performance hit if you are using it as a FK in another table.
 
S

salim

New member
Local time
Today, 03:57
Joined
Mar 18, 2008
Messages
9
Thanks Rabbie for the help, but all I am trying to say is that I cannot do it, anyway I am sure there is something mixed up all along, anyway a quick question, do we set the FK as PK? say vehicle_type is PK in the table Vehicle_Type_tbl and FK in Vehicle_tbl, when I assign it as such do I assign is like I do with PK, i.e it will have the key icon as well in the Vehicle_tbl?
 
Rabbie

Rabbie

Super Moderator
Local time
Today, 03:57
Joined
Jul 10, 2007
Messages
5,906
salim said:
Thanks Rabbie for the help, but all I am trying to say is that I cannot do it, anyway I am sure there is something mixed up all along, anyway a quick question, do we set the FK as PK? say vehicle_type is PK in the table Vehicle_Type_tbl and FK in Vehicle_tbl, when I assign it as such do I assign is like I do with PK, i.e it will have the key icon as well in the Vehicle_tbl?
Click to expand...

You must read up on how relationships work in relational databases. You need to be clear as to what the relationship is. Is it 1 to 1, 1 to Many or Many to Many. This will determine how you do it. Try searching for relationships on this forum. I would advise you to read up on database design before you dig a really deep hole for yourself.
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 03:57
Joined
Sep 12, 2006
Messages
15,656
how can the vehicle table have a compound primary key

a vehicle regno must be unique (i would use an automnumber, personally), but thats beside the point. The registration no. MUST be a candidate for the primary key. surely.

then you can make your realtionship between the vehicle table, and the bookings table.

---------
the point about autonumbers is

a) they dont change
b) numbers are more efficient

what happens if yuo change a registration number - eg put personalised plates on - yes you might get cascading updates, but you dont realy want that. If you use an autonumber key, you can just amend the regno without a care.
 
G

georgedwilkinson

AWF VIP
Local time
Yesterday, 21:57
Joined
Mar 4, 2008
Messages
3,856
Listen to him...he is wise beyond his years.

But seriously, in your vehicle table you will have:
ID (autonumber-PK)
Regno text (UK)
The other stuff.

You can have as many UKs as you want, but make sure the PK is an autonumber field and it is the source of your FKs.

When you create your links between tables, you drag the ID (PK) to the corresponding field (FK) in the other table (I assume that would be vehicle_reg).
 
S

salim

New member
Local time
Today, 03:57
Joined
Mar 18, 2008
Messages
9
georgedwilkinson said:
Listen to him...he is wise beyond his years.

But seriously, in your vehicle table you will have:
ID (autonumber-PK)
Regno text (UK)
The other stuff.

You can have as many UKs as you want, but make sure the PK is an autonumber field and it is the source of your FKs.

When you create your links between tables, you drag the ID (PK) to the corresponding field (FK) in the other table (I assume that would be vehicle_reg).
Click to expand...

you said I will drag the PK to the FK, my question is: is the FK in the other corresponding table where I drag to would be set as normal as an attribute or do I have to assign it like I assign any PK, i.e to have the key icon, in other words should the FK stay normal, or would I have to assign it differently, I know it's already assigned as a PK from where I drag from, but do I have to do the same in the other table where I drag it to, I hope I am clear in my question.
thanks
 
Rabbie

Rabbie

Super Moderator
Local time
Today, 03:57
Joined
Jul 10, 2007
Messages
5,906
salim said:
you said I will drag the PK to the FK, my question is: is the FK in the other corresponding table where I drag to would be set as normal as an attribute or do I have to assign it like I assign any PK, i.e to have the key icon, in other words should the FK stay normal, or would I have to assign it differently, I know it's already assigned as a PK from where I drag from, but do I have to do the same in the other table where I drag it to, I hope I am clear in my question.
thanks
Click to expand...
In the table where it is a FK it should not have the key icon. That table should already have a PKmof its own. look at some of the sample DBs on this forum to see how it's done.
 
S

salim

New member
Local time
Today, 03:57
Joined
Mar 18, 2008
Messages
9
yep, it's been a long time sisnce I haven't done this, anyway talking about the end 1:m, if the PK is autonumber, the FK should too, but how could I set the relationship, it's not allowing me
 
Rabbie

Rabbie

Super Moderator
Local time
Today, 03:57
Joined
Jul 10, 2007
Messages
5,906
salim said:
yep, it's been a long time sisnce I haven't done this, anyway talking about the end 1:m, if the PK is autonumber, the FK should too, but how could I set the relationship, it's not allowing me
Click to expand...

No. If the PK is Autonumber then the FK should be a Long integer not an autonumber. It should be indexed with Duplicates allowed. Why don't you post a copy of your DB so we can help you.
 
S

salim

New member
Local time
Today, 03:57
Joined
Mar 18, 2008
Messages
9
I tried to upload the my db file but it says invalid file...anyway i got it sorted for now, I hope I wont get stuck somewhere, thanks for your help, much appreciated
 
S

salim

New member
Local time
Today, 03:57
Joined
Mar 18, 2008
Messages
9
Rabbie said:
No. If the PK is Autonumber then the FK should be a Long integer not an autonumber. It should be indexed with Duplicates allowed. Why don't you post a copy of your DB so we can help you.
Click to expand...

I tried to upload the file but it says invalide name...? it did work the stuff,thanks for your ehlp, I might need some more as i will have to implement the system for the small company, that's if nonme of you mind helping, it's form and inserting data, like when we receive order then we have to put it in the system, something like that, anyway thanks
 
Rabbie

Rabbie

Super Moderator
Local time
Today, 03:57
Joined
Jul 10, 2007
Messages
5,906
Glad to be of help. If you need more advice just post it on the forum
 
You must log in or register to reply here.

Similar threads

N
Solved Issues with query joins
Replies
11
Views
322
nashaz
N
S
Insert Into Query for 1:m Relationship
2
Replies
36
Views
1,011
Solo712
Solo712
tltanhueco1990
Scenario to RDBMS in SQL Server
Replies
4
Views
409
Pat Hartman
P
Notiophilus
Subform cannot be referenced when placed in a hidden footer - why?
Replies
7
Views
537
Notiophilus
Notiophilus
C
Hard problem with query
Replies
6
Views
431
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom