The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 18, 2019, 07:31:23 PM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: Unknown relations in database  (Read 3283 times)
kurdt
Lifer
*****
Offline Offline

Posts: 1153


paha arkkitehti


View Profile
« on: October 15, 2009, 12:33:51 AM »

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 Smiley

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?
Logged

I met god and he had nothing to say to me.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #1 on: October 15, 2009, 07:20:54 AM »

Can you give an example of the actual tables?
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #2 on: October 15, 2009, 08:12:04 AM »

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

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
kurdt
Lifer
*****
Offline Offline

Posts: 1153


paha arkkitehti


View Profile
« Reply #3 on: October 15, 2009, 08:36:32 AM »

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

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.
Logged

I met god and he had nothing to say to me.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #4 on: October 15, 2009, 09:05:02 AM »

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.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #5 on: October 15, 2009, 09:08:32 AM »

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.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #6 on: October 15, 2009, 09:09:25 AM »

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

I could eat a bowl of Alphabet Soup and shit a better argument than that.
kurdt
Lifer
*****
Offline Offline

Posts: 1153


paha arkkitehti


View Profile
« Reply #7 on: October 15, 2009, 10:12:28 AM »

ah ok. Im also assuming that existing table structure cannot change, but can be added to correct?
Yes.
Quote
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 Smiley
Quote
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 Cheesy

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? Cheesy
Quote
My immediate assumed guess is you need a lookup table.
Could be... if I just knew what lookup table is Cheesy

Quote
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.
« Last Edit: October 15, 2009, 10:14:39 AM by kurdt » Logged

I met god and he had nothing to say to me.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #8 on: October 15, 2009, 10:27:14 AM »

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?
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #9 on: October 15, 2009, 11:55:59 AM »

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.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
kurdt
Lifer
*****
Offline Offline

Posts: 1153


paha arkkitehti


View Profile
« Reply #10 on: October 19, 2009, 12:04:07 AM »

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

I met god and he had nothing to say to me.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #11 on: October 19, 2009, 03:30:59 PM »

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

I could eat a bowl of Alphabet Soup and shit a better argument than that.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!