Any ideas to improve this schema?

[Zeratulsdomain]

Basically I am designing a fixed asset DB (inventory).

I don't have visio, so anything in bold a entity/table… this is also the breakdown of what I have right now (don't want to give to much info incase I am not supposed to).

item
PK Autoincrement_surrogKey
Item
Item
Item
Bool IsItLeased //can either be leased or purchased
Bool Disposed
Bool Linked

----------------

Leased
PK FK Autoincrement_surrogKey
Item
Item
Item

Note: entries from leased will be there is IsItLeased (in Item table) is set to true.

----------------

Purchased
PK FK Autoincrement_surrogKey
Item
Item
Item

Note: entries from purchased will be there is IsItLeased (in Item table) is set to false

----------------

Disposed
PK FK Autoincrement_surrogKey
Item
Item
Item

Note: entries from Disposed will be there if Disposed (in Item table) is set to true

----------------

Linked
PK FK Autoincrement_surrogKey
PK FK Autoincrement_surrogKey

Note: entries from Linked will be there if Linked (in Item table) is set to true

----------------

That is basically it. Is that done right? Or is there a better way to do all this? Basicly a bunch of 1-to-1 optional relationships. I suppose I don't even need to bool variables in Item and could just do a check on all the 3-4 tables to see if there is a proper entry, but that seems inefficient.

[Hack]

Well, the first couple of questions would be, what kind of a database are you using.

Also, is this schema working for you right now?

[Zeratulsdomain]

well the database will be MS SQL, and the schema is currently in the design phase so I am trying to get opinions on it. I hope there is nothing wrong with it :P

Overall seems fine. I suppose the biggest thing I am currently wondering is if having the bools is a good idea. Because I could technically just code my program to always check the other DB's if the PK is there (more CPU power) or using my bool method (more HDD space)

This is my first time designing one on my own also the first time designing one that will be used (not a school one).

[szlamany]

I'm not sure how the bools are going to help you.

How does ITEM and LEASED get joined in a real select statement? What field is common between the two??

[Zeratulsdomain]

I'm not sure how the bools are going to help you.

Well if they are there I could do a select statement on Item and know information on the item.

Right away I know if its leased/purchased or if its disposed or linked. If its not disposed or linked then that is three select statements I do not have to do with Autoincrement_surrogKey on those other tables.

So it could save up to three queries per item. Basically because a record in "Item" might not be disposed or might not be linked I figured it was the best path.

How does ITEM and LEASED get joined in a real select statement? What field is common between the two??

Well the item can only be leased or purchased, not both. So using the bool I know right off the getgo what table to check so I just do a "select" check the Autoincrement_surrogKey to link it to item.

So its a one-to-one relationship Autoincrement_surrogKey being the foreign key (and the PK) in either the leased or purchased tables.

Both leased and purchased seem to be exclusive subtype entities.

[szlamany]

Why have a LEASED and PURCHASED table - what is the difference? What is the benefit of having these two tables separate.

[Zeratulsdomain]

Leased has two fields of information that needs to be inputted, purchased has a total of 5 other unique fields that needs to be inputted.

So by having two tables there should be no nulls. If it was on the same table there would always be nulls and my program would have to figure out which one of the two fields is the correct one (by checking for nulls I guess :P )

[szlamany]

Either way you are checking for this fact.

If separate the two tables you still have to "know" which one to go to on the JOIN.

If the tables were together then you still have to figure out what fields are part of that item.

I can see having the tables separated.

We have an ACCOUNTING package that has a main table called LEDGER.

There are times that LEDGER entries have related PO records. And sometimes they also have related INVOICE records.

We simply have a field call POEntry and InvEntry - it's an int field that points to the int PK of those two other tables.

If the LEDGER record does not have a PO associated then the POEntry is set to the value 0. InvEntry can also be set to 0 if no INVOICE associated with this LEDGER.

Select's are always

Select * From Ledger_T LE
   Left Join PO_T PO on PO.POEntry=LE.POEntry
   Left Join Invoice_T IV on IV.InvEntry=LE.InvEntry

Just the fact that the 0 or a value is in the POEntry field is enough to tell code further down the line if this LEDGER has an ASSOCIATED PO. There is no reason in my mind for a BOOL field - the fact is clear by the FK value itself being in the LEDGER record.

[Zeratulsdomain]

Well I would not need to check for it as long as I kept those bool updated. So if its false for Disposals then I do not need to check disposals table for a entry.

I will know which table to go on the join based on the bool

================
select * from items

if Disposed = true then
select * from Disposed where var.surrogKey = item.surrogKey

etc
================


Also the reason having one int field pointing to a other PK field would not work is because its possible mutliple items will be linked to each other (PC to monitor, PC to router, PC to keyboard, etc). Using that other table lets the user add as many items they want.

[szlamany]

================
select * from items

if Disposed = true then
select * from Disposed where var.surrogKey = item.surrogKey

etc
================

But - that is not how SQL wants to work.

You are not doing a JOIN here - you are doing additional queries to the tables.

Usually you want to avoid that at all costs.

It's better to say

Select * From Items
   Left Join Disposed on Disposed.surrogKey = item.surrogKey and Disposed=True

[Zeratulsdomain]

hmmm, well let me think about this...

=========
This would obviously easily work for disposals (just ad a new auto incremental surrogate key there and make that a FK in Item).
(edit: did this, one bool gone)
=========
As for leased/purchased: I could always get rid of the bool altogether and just do

select * from Leased, Purchased, Item
where (Item.SG = Leased.SG) or (Item.SG = Purchased.SG)

then get VB to throw away all the Item variables that I do not need.
=========
but for linked table it would not work, since a FK has to be a PK from a other table.

[szlamany]

There is school taught "proper" methods for 3rd normal form and design...

And then there is real world - where you have to be able to create simple to read and modify queries and easy to manipulate recordsets.

You have to land some where in the middle in order to remain sane!

Like BOOL fields - in ACCESS it would be a boolean that would store a 0 or 1.

In MS SQL - we prefer to use Y or N fields. Makes it easy to understand visually.

But back to your linking table - if we are going to continue this you are going to have to mock up 3 or 4 sample items so I can grasp the reality of what you are talking about.

btw - you don't always have to have a "link" - LEFT JOIN allows for no sister rows to exist - so dropping a 0 into a field for the link ID and having the JOIN simply fail to find a record is fine in my book.

[Zeratulsdomain]

well I get to go home now (hungry right now 2 been here 1 hour later and my legs hurt for some reason)...but here is how that would work with linked (lets say it has 1 field)

Item
Item_Num
IsItLinked

Linked
Item_Num 1
Item_Num 2

Data in ITEM
1 false
2 true
3 true
4 true
5 false
6 true

Data in LINKED
2 3
2 4
4 6

So now when I see entry 2 is linked, ill simple checked both rows of table Linked and see what products they are linked to.

That is a very simple example.

I just cant think of a simple efficiant way of hadling it without bool.

Anyway, going home now.

[szlamany]

Here is something we have done.

We have a MASTER_T table - stores people - usually people in a family.

In this case there is a "primary" person.

Master_T table

MasterId int PK
RelatedMasterID int
MasterName

values can be

MasterId   RelatedMasterId  MasterName
--------   ---------------  ----------
   1              1         SMITH, JOHN
   2              1         SMITH, ANN
   3              1         SMITH, BOBBY	-- that was all one family

   4              4         ROGERS, ALAN

   5              5         DAVIS, ROB
   6              5         DAVIS, JUDY

Selecting from this table and ORDER BY RELATEDMASTERID,MASTERID will put all rows together for a family with the "primary" person first.

That RELATEDMASTERID could easily be a "GROUPING" value of any type. Like "A" for the first family, "B" for the second, "C" for the third. It just groups them together.

What I don't like about your linking table is that you have to see if the link'd ID is in either of the two columns - and you have to use the BOOLEAN to determine whether to go there. That's unnatural for me.

[Zeratulsdomain]

well my link table does the exact same thing you are suggestion except I use two tables Item and Linked. The only difference is that my Item table (vs your MASTER_T table) has many different entries (16). So instead of having it all in one table were there would be loads of redundant data, I use the link table in the exact same way you use the MASTER_T table.

one thing I just realized while writing this is the fact I dont know how to write SQL statements for optional fields...

for example
(test1= Item, test2 = Linked)

select * from test1, test2
where test1.id = test2.id
and test1.id = 1 -- note: 1 would be any id field number

That will only work if the item is linked to anything, or else it wont work (wont display anything and id be forced to use the bool with two SQL statements).

That being said, I suppose I have the exact same problem with Disposed were id have to use two separate SQL statements.

I suppose I could do what you did and have the item linked to itself, but that would not solve the disposed problem of using two SQL statements + in my situation it seems a tad messy.