next »

[kurdt]

Here's the problem. I have a table of records but I have no constant relations so I can't do specific columns for foreign keys. Basically at any time there can be record that refers to id in table that was created & populated few minutes ago and amount of these references is also uncontrollable. But I still need "master" record table to keep some sort of logic. I know this sounds very idiotic way of doing database but try to look past that and solve the problem

What would be the best way to store relations to ids? Maybe serialized array? When you fetch the record you read that array, do the queries and join? It would add only 1 extra query to the mix. Any other suggestions?

[nutballs]

Can you give an example of the actual tables?

[perkiset]

yes, and some data too (munged, obviously) along with a notion of how you'd like to see the eventual data output look

[kurdt]

Ok, so here's an example. Hope you can work with this

Master record table is a list of technical products. Then there's a table for transistors. That table holds all the info about different transistor models. I don't know anything about transistors but for sake of example let's say it holds data of how much voltage it can take, who manufactured it and what it costs. Then I have table for processors that holds all the info for different processor models. Now as you can see when it comes to technical products, there's always need for new tables. Let's say I want to add record for dishwasher and I have to make new table for all the plastic parts. It would be very unpractical to endlessly expand the master records with new columns.

And the idea is that usually only master record is fetched for the name but in need it can be expanded so you get all the details about the product down to manufacturer of plastic part X. I don't have any specific data but I hope this information is enough for you guys to grasp what I'm trying to achieve.

[perkiset]

This is a normalization project.

Roughly, you want one big table for all parts, Then you'll want tables that define attributes, and another one for master products for example. Then you'd use cross-refrence tables to link a product (dishwasher) with all of it's components (cross-ref'd to the parts table). By having an attributes table and cross reference, you can start base queries of "show me all my transistors." So you might have a major and minor id in each part record that points to a category record, or have anot cross reference table that links a part to an attribute.

As soon as you say, "endlessly expand the master record..." you've shown enough to call for joins and normalization. If you're concerned about speed of display, you could use Views which would make a considerably amount of difference.

[nutballs]

ah ok. Im also assuming that existing table structure cannot change, but can be added to correct?

So each product type has its own table for holding the details about the product.
This was to deal with the fact that a transistor has completely different information than lets say, a logic chip.
Call this the Parts Table

And then the master is just short "common" info, like name/price/description, and it hold an entry for each part.
OR
The master table is for a parent product, like a "dishwasher", and the Parts Table for that product holds all the little parts that make it up.
OR
The master table is for a part type, like "transistors", and then each specific transistor is in the "transistors" Parts Table.

Is there anything common between the two tables? Or is is literally that each row in the master table, has a Parts Table?

My immediate assumed guess is you need a lookup table.

[nutballs]

oh and dont worry about a retarded database. I have a winner that everyone here would look at and think I was completely nuts.

[kurdt]

ah ok. Im also assuming that existing table structure cannot change, but can be added to correct?

Yes.

So each product type has its own table for holding the details about the product.
This was to deal with the fact that a transistor has completely different information than lets say, a logic chip.
Call this the Parts Table

Yes, exactly what I was thinking

And then the master is just short "common" info, like name/price/description, and it hold an entry for each part.
OR
The master table is for a parent product, like a "dishwasher", and the Parts Table for that product holds all the little parts that make it up.
OR
The master table is for a part type, like "transistors", and then each specific transistor is in the "transistors" Parts Table.

Is there anything common between the two tables? Or is is literally that each row in the master table, has a Parts Table?

Well to make things complicated, technical product can be also transistor

What I was thinking was something like this:
- There's a thing called dishwasher
- Dishwasher record contains info about pricing, manufacturing of that unit, etc. misc. info AND that it's made up with transistor X, Y and Z and uses processor D.
- Problem is that dishwasher is only one example and product can contain X amount of parts with quantities of Y.

Now I'm not sure if it makes any sense to do a separate parts table because it faces the same problem of unknown amount of parts. But it just came to mind that what if I would build a paired list table. Meaning that it would be a simple list of like this:
root_item_id, part_item_id
root_item_id, part_item_id
etc...

This way it doesn't matter how many parts there is and it doesn't matter how many tables there are. And it also takes care of that transistor as product problem because now everything can be a product and can be a part. Does this make any sense?

My immediate assumed guess is you need a lookup table.

Could be... if I just knew what lookup table is

Roughly, you want one big table for all parts, Then you'll want tables that define attributes, and another one for master products for example. Then you'd use cross-refrence tables to link a product (dishwasher) with all of it's components (cross-ref'd to the parts table). By having an attributes table and cross reference, you can start base queries of "show me all my transistors." So you might have a major and minor id in each part record that points to a category record, or have anot cross reference table that links a part to an attribute.

Is my idea of paired list what you meant with attributes table? If I understood correctly what you are saying here, it should work like that.

[nutballs]

ok ok.

So IF this was being done from scratch it would be this:

tblProducts -ProductID -Name -Description -Price -etc

tblProducts_Parts_lookup -LookupID -fk_PartID -fk_ProductID

tblParts -PartID -Name -Description -etc

tblPartAttributes -AttributeID -fk_PartID -Key (this is a friendly name like color or width or weight or X) -Value (the actual value for that key)

How does that fit?

[perkiset]

that's precisely where I was going as well nuts - just much better said. I'd also add one more, but this should spin gears about going forward. You'd need to add fk_VendorID to this schema as well. Personally, I use the name "id" for a table, then the name of the table as the FK, and I use plurals (or not) to represent relationships. so I'd do the tables like this:

Vendors:
==============
id (pk)
name (indexed)

Materials
==============
id (pk)
description

Products
==============
id (pk)
vendor_id (indexed)
product_code (indexed)
description
price
(etc)

Parts
==============
id (pk)
material_id (indexed)
description

Parts_Products_XRef
==============
product_id (this + part_id = pq)
part_id (indexed + product_id)

Note that you'd not need an ID for the xref because you simply make the PK reliant on both fields and you've built uniqueness into it. I'd also add an index that reverses the PK, parts_id + product_id so that you could look things up in either direction. If a part is made up of multiple materials, you'd need a cross reference table for that as well however.

So grabbing all the parts involved in a Bosch dishwasher (model 6900) that were made of plastic would look something like:

select
   parts.description
from
   vendors, products, parts, parts_products_xref, materials
where
   vendors.name = 'Bosch' and
   products.vendor_id = vendors.id and
   products.product_code = '6900' and
   parts_products_xref.product_id = products.id and
   parts.id = parts_products_xref.part_id and
   materials.id = parts.material_id and
   materials.description like '%plastic%'

although it looks rather like a big join, this would actually sort out damn quickly. If it was created into a view it'd be really fast.

[kurdt]

Excellent! Thanks guys I'll try Perk's schema because it's like Nuts but little bit more expanded

[nutballs]

yep I would to. I just wanted to focus on the core issue.