Solved Correct builiding step (1 Viewer)

[lacampeona]

Hello experts,

I have some questions

1. You have to create a database to track specific items, their entry into database and their usage. You have to build 4 different type of items.
Lets say you have to mantain Cars, Books, Medicine, Reserve Parts and all their usage.

How would you make that?

Will you create 4 tables of items (tblCars, tblBooks, tblMedicine, tblReserveParts) and 4 tables for their usage tblCarsUsage, tblBooksUsage, tblMedicineUsage, tblReservePartsUsage)

and then you will create a relationship for (tblcars+tblCarsUsage),(tblBooks+tblBooksUsage), (tblMedicine+tblMedicineUsage), (tblReserveParts+tblReservePartsUsage)?

is that correct way?

what if then someone in the future say to you that he wants new item? lets say you have to mantain Essential Oils and their usage?
what you will do? Create again new table for Essential Oils and their usage? and create their relationship? tblEssentialOils+tblEssentialOilsUsage?

I am just all confused becouse all these items has their specific you can not connected then with nothing similar? hmmm
My concept for now is working but I am all the time thinking that maybe I didnt start correctly.

How would you experts make this things? What is the correct way?

Thank you very much

 

[tvanstiphout]

No, that is very much incorrect.
These are all "Products", or "Items", or whatever term you want to use. They go in 1 table with ProductID (PK) and ProductName (text50, required, unique index).
Then a single table of Usage, with UserID FK, ProductID FK, DateUsed, DurationUsed, whatever is appropriate to you.

 

[lacampeona]

Hi Tom,
I have a request that every item has to have their special ID. So I create ProductID (PK) and then my special ID is another field?
So then I will have in one table fields with:
ProductID=1
ProductID=2
CarsID = Car2023-001
MedicineID = Med2023-001

hmmmmmmmm

 

[tvanstiphout]

The Products table can have an additional field, let's call it ProductCode (text20, required, uniqueindex), which you an format as you see fit.
It will likely also have a ProductCategoryID (FK, Long Integer, Required).
The new Northwind 2 Developer Edition template has an example of Products and ProductCategories. See if that makes it more clear for you.

 

[lacampeona]

Hi
Ok I thanks for your explanation.
I will search the Northwind 2 Developer Edition template

 

[GPGeorge]

Hi
Ok I thanks for your explanation.
I will search the Northwind 2 Developer Edition template

Available here.

 

[lacampeona]

Hi GP George
Thank you
I will start resarching the database.

 

[Pat Hartman]

Do you think Amazon has a separate table for each type of object they sell?

 

[The_Doc_Man]

The thing that might be confusing you is that you can have more than one unique ID per record if they have different purposes.

For relational purposes, you might have a record ID as the tables true Primary Key, perhaps with autonumber. But there is nothing wrong with having a category indicator (car, book, medicine, parts) and having a second ID number that is maintained uniquely to that category. You DO NOT build relationships off this secondary ID... or if you do, it potentially would involve a table that joins to the central table on the item type. But the secondary ID could be unique. If for example you actually DID have a car as one of the item types, the car's VIN would be a great secondary unique ID field. The trick, as always with this kind of "disparate membership" problem - is knowing what goes in the main table and what goes in secondary tables. It requires a bit of thought. But it has been done.

If you do it this way, you can have any reasonable number of item types by defining new item codes.

 

[lacampeona]

Hi Pat,

Do you think Amazon has a separate table for each type of object they sell?

Yes I know that is imposible to have separate tables and that this is not correct.

I am just confused with my special ID but I will change all my concept and start again.

 

[lacampeona]

Hi DocMan

The thing that might be confusing you is that you can have more than one unique ID per record if they have different purposes.

Yes yes I am confused becouse I never seen something like that and becouse of that I am thinking totaly wrong.
I will start again building from the start.
thank you

 

[GPGeorge]

Hi DocMan

Yes yes I am confused becouse I never seen something like that and becouse of that I am thinking totaly wrong.
I will start again building from the start.
thank you

In the Northwind Developer Edition, the Products table has an example of this.
The table has the standard AutoNumber Primary Key which is used in relationships with other tables. However, it also has a unique ProductCode, which is a combination of Letters and Numbers, which is "human friendly".

As a matter of fact, it is possible to use the ProductCode to generate the SKU for each product and encode it in a bar code. And you can then create a label with the bar code printed on it for use in Stock Take operations.

It has to be unique, of course, although it is not the Primary Key for the table.

 

[gemma-the-husky]

Hello experts,

I have some questions

1. You have to create a database to track specific items, their entry into database and their usage. You have to build 4 different type of items.
Lets say you have to mantain Cars, Books, Medicine, Reserve Parts and all their usage.

How would you make that?

Will you create 4 tables of items (tblCars, tblBooks, tblMedicine, tblReserveParts) and 4 tables for their usage tblCarsUsage, tblBooksUsage, tblMedicineUsage, tblReservePartsUsage)

and then you will create a relationship for (tblcars+tblCarsUsage),(tblBooks+tblBooksUsage), (tblMedicine+tblMedicineUsage), (tblReserveParts+tblReservePartsUsage)?

is that correct way?

what if then someone in the future say to you that he wants new item? lets say you have to mantain Essential Oils and their usage?
what you will do? Create again new table for Essential Oils and their usage? and create their relationship? tblEssentialOils+tblEssentialOilsUsage?

I am just all confused becouse all these items has their specific you can not connected then with nothing similar? hmmm
My concept for now is working but I am all the time thinking that maybe I didnt start correctly.

How would you experts make this things? What is the correct way?

Thank you very much

I think it's also a curious query. I can't conceive of a database where you wish to manage cars, books, medicines, and spare parts, and I find it awkward contemplating hypotheticals.

As an example though, consider a company accounting system where you store names and addresses for customers, suppliers and employees. Some persons may be in more than one category. So it's a matter of taste whether you have separate tables for each different person (and therefore the same person may appear more than once), or you arrange to have a single table for all.persons, and manage their use by attribute flags. Either of these will work, but you design the database differently depending on your choice.

 

[lacampeona]

Hi to all
yes I just see so much examples and all that has a logic....but my database is very very special...
I also make some exercises to build a relationship that i find on internet from Rogers library and I must say that all that was logic and i dint make so many errors...but my database is realy very special with so strange wishes from the users..
I will need a lot of time to explain what is all about that...
what is now confusing me...
ok I have tblUsage for items... now i have 4 tables for every items of usage...I know that is not correct and I am trying to build all again from the begining

My problem number 1:

- every item has diferent fileds of the usage? items do not have all the same fields...If I put all in one table then i will have some empty field?

hmmm

 

[CJ_London]

every item has diferent fileds of the usage? items do not have all the same fields...If I put all in one table then i will have some empty field?

So what if you do? They don’t take up any space and can be hidden on forms if not relevant to the object type. There will be some common fields and some fields that could be ‘ multi purpose’

consider a warehouse storing everything from a tin of paint to a bulldozer - they will just use one table.

You can have some child tables to the main table to store specific data if really required.

 

[lacampeona]

Hi
Ohhhh this is good then I thought that if I have empty fields that this is not ok

 

[The_Doc_Man]

Consider that there is a thing called an EAV layout. "EAV" is "entity, attribute, value" and it is almost impossible to find something that cannot be described using EAV. But it is tricky to use sometimes.

With an EAV layout, your entity is defined at the top level. It has an Entity ID, a name, and a general type identifier. There might be a few other things you track, perhaps some codes or descriptions or something. BUT all of those fields that you worry about being blank don't exist. Instead, you can put a child table with the Entity ID (so you can make a formal one/many dependency relationship), the name or code for an attribute, and the value of that attribute (most commonly in text format).

I ran across a practical example of that with a genealogy database where you had persons, names, gender, and a few other things in the main record, but then other attributes went into the child table. Things like date and place of birth, residence on a certain date, date and place of death, date and type of college degree, ... In the Ancestry.COM databases, there are well over 250 possible attributes, all defined in EAV format.

Using EAV, there are no blank fields. If you have nothing for that field in the EAV case, there is no child record for it. This is technically called a "sparse" layout, meaning that if you don't know, you don't have a place-holder for the unknown attribute. You have NOTHING because you don't bother to record "nothing." Sounds like a lot of craziness and complexity, but a DLookup can still find a person's birthplace in one step, by querying the child table using the criteria of the person's ID and the code for "birthplace." In the Ancestry database, there are a LOT of blanks because some of the records are incomplete. More than one set of records has been lost when a pre-1800s community church burned down, losing birth and death records.

I don't know if you actually want to go the EAV route, but since you were asking about ways to layout potentially disparate entities in a single table, this is one way to do it. Pat Hartman has described the EAV layout in many of her posts. You might search for it.

 

[lacampeona]

Hi Doc Man
thank you for your explanation.
Ok I will search also for EAV layout from Pat.

 

[CJ_London]

A common eav example is contact methods- mobile/cell, landline, fax, email, LinkedIn etc

each one in a separate record containing fk to contact, the method and the value

 

[jdraw]

EAV info from wikipedia