Sign in to follow this  
Chicago

need help with database structure

Recommended Posts

attention DB Admins!

I know this is a storge forum so please point me in the right direction if you know of a good and knowledgeable site. And by all means if you have any good suggestions to my problem please lend me your advice.

I am looking to create a new parts database and here is my problem:

I need to store information on 10,000 products, there related parts, and the number of each part per product. Here is an example

{product number}{product description}{part0 number}{part0 amount}{part1 number}{part1 amount} etc.....

there may be as few as two parts to each product or as many as 50 parts. The variable parts section lends itself well to an array however i dont know of a good way to store an array into a field, and i dont know if its a good idea to create a separate parts table for each record either(10,000+).

this will be a web based application when its build however i need to sniff out the better canidates for data storage and development language. I am new to both and with not play a heavy part in either all said and done.

thanks in advance...

Share this post


Link to post
Share on other sites

This data model for this is two tables in a classic one-to-many relationship.

Table: Products
productnumber     integer
description       string


Table:  Parts
id                integer
productnumber     integer
partamount        integer

now let's take a typical product:

product 11345 with 100 part 6567754 and 20 part 565623435

This translates into the following database entries:

Products:
number        description
11345         foobar

Parts:
id            productnumber     partamount
6567754       11345             100
565623435     11345              20

The trick is the productnumber field in the Parts table. This relates a specific part to a product. You can follow this relation in two ways: From a part, find out what product it belongs to. Or from a product, find out what parts are in it. This is how you store an array of parts , by putting all the parts in a table linked to the same product by the productnumber field.

This is a classic one-to-many relationship between Products and Parts. One Product can have many parts but one speific part belongs only to a single product. Notice that it doesn't matter how many parts are in a product. These tables can handle parts with 50 or 5 million parts (at least theoretically).

Now, on the GUI side of things, this is often described as a Master-Detail relationship. That's because there's usually two controls, one for Products (on the left) and the other for parts (on the right). When you select the product on the products control, the parts for that product automatically appear in the other control. This is such a common construct that many toolskits can do this automatically provided the database tables are setup like I should you.

As for web based development, what is the skill level of the developers. If they're open to something completely new, give Ruby on Rails a try. It's a good fit for a simple application. For the neophyte, Dreamweaver with phAKT model is good choice.

I haven't given you any resources to check out, but hopefully, I've given you some nice phrases to google.

Edited by Zark

Share this post


Link to post
Share on other sites

thanks for the response, after further looking into this situation(other factors) i think this would be a classic "many to many" relationship and needs a "associative entity". The days of when I messed around with access tables are comming back to me. Ugh. I thought there might have been a better way developed in the last 8-10 years. Anyways i have my answer and appreciate the help

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this