help needed for database design

[vinux]

Hi,

I want to make an access database but Im struggling with the relations a bit.
I understand the one-to-many and many-to-many concept but seems like there is more then that

I want to make a database that keeps track of things we lend.

I have several items with different properties we lend out:

eg

TANK
-ID
-Volume
-Weight

MACHINE
-ID
-hours
-fueltype

...

I made the following tables to keep track of it:

RENT
-ID
-Customer
-...

One-to- many relationship between these

RENTLINE
-ID
-RENT_ID
-Item


My problem lies with this Item.
I want this Item to have a relation with the ID of the different items we lend out. there needs to be a one-to-many relation here.
one rentline can exist of one TANK OR of one MACHINE.
A TANK or a MACHINE can exist on many orderlines.
So I made 2 one to many relationships from Rentline to the id's of Tank and Machine

the problem is that Access does not let me fill in the rentline with the id of a item. If I fill in the ID of a TANK it says a ID of a machine is needed and vice-versa.

I tried to solve it with an intermediate ITEM table that looks like this :
ITEM
-ID
-Sort ( Tank or Machine)
-ItemID (Id of the tank OR the Machine)

But this generates the same issue as directly in the rentline...

I guess this is pretty common stuff Im asking here but its confusing me like hell

thanks in advance for all advice

 

[vinux]

thanks for the advice.

I renamed my ID's.
Im still strugling with that one - to - one relation.

Ive got a Material table now wich looks like this:

Material
Mat_ID
Mat_Supplier
Mat_PurchaseDate
...
Mat_DetailID

that Detail ID should then be the 1 to 1 with the idea in the machine or tank table.

When thinking about it is quiete normal the DB does not let me make two 1 to 1 relations with relational integrity here...
Do I need to add a field that can contain a ID for every type of material I got?
Is this referential integrity required?

in the future I would like to have the ability for the user to create a new kind of material and the properties of it in another table. eg they recently descided to start renting bulldozers of which a complete other set of data properties needs to be held.

What model would be ideal for this?

Greetings,

( I might need an expert programmer but we are a starting firm without the resources that are required to pay for these expensive guys . So I'm trying first to build something on my own...)

 

[ScottGem]

If I understand, you took Pat's correct advise to create ONE table for the items you rent. And then to have additional tables for each class of item to hold the info unique to that class. You would then have a one to one relation between the ONE table and each class table. In that case the MaterialID becomes a foreign key in the CLASS table you wouldn't have a mat_DetailID in the Material table.

 

[vinux]

right,

How do I set a foreign key in access?
I can only see Primary key.

thanks for the advice

 

[ScottGem]

You don't "set" a FK like you do a PK at least not in table design mode. An FK is simply a field that holds the value of the PK of the corresponding records. So is you have a material record with a MaterialID of 101 then the MaterialID in the Detail record would have a value of 101.

The only place where FKs are identified in any way is in the Relationships window. They are identified as being the other side of a join with a PK.