I'm a rank newbie at databases, designing a database to
keep track of purchases and inventory at a small company.

What would be the standard way(s) to design a database
where each record has a list of variable length associated
with it? Specifically, a purchase has a list of items
purchased associated with it. Sometimes it's just one
item, sometimes 10, sometimes it's 30 items.

Should I make a table for purchases and a table for
purchased items and have a one way link from a purchased
item record to the purchase it was part of?

Is a linked list a better idea, with a link from the
purchase to the first purchased item, and then links from
item to item after that?

Is there a better way?

Wayne Busby wrote:
Do a GOOGLE search for Normalization of a relational database model. Go
read some of the University sites that discuss it. Also look up Entity
Relationship Diagrams (ERD) You should actually start with an ERD.

But to answer you more directly:
This is a clasic Header Detail pattern. A linked list is something you
might use in your program, NOT in your database! You create a header
table, called maybe SALES INVOICE. The Primary key is the sales order
number. THen you create a SALES ITEM table. the primary key for it is
order number and line number. There is a foreign key from the order
number in the ITEM table to the PK in the INVOICE table. (but even that
leaves out lots of details like how do you handle sales of items not in
stock, calculating total due, etc.)

Good Luck. You are going to need it.
Ed

PS Nothing against you, but the company really should consider using an
off the shelf product for sales and inventory. Having an inexperienced
developer write one from scratch is, in the long term, a waste of money.

Ed Prochak wrote:
Thanks for the advise.

Actually, it is my company. I have a few reasons for not wanting to go
with an off the shelf product.

First, they're expensive.

Second, off the shelf products tend impose restrictions, like which OS
they support, etc. If I'm forced to use Windows for this for instance,
it's going to introduce more headaches and maintenance problems than
if I have to model my own database.

Third, I almost always find myself frustrated by stock software.
There's always some quirky limitation, or lack of imagination, or
something that wastes my time. I'm a control freak and I like being
able to tailor software to how I work.

Fourth, I want a competitive advantage, so I want to be able to
do my own specialized analyses, things that a stock program may not be
able to do.

Finally, I need to learn about databases sooner or later anyway and
I need to do a lot of other things with databases. I learn very fast,
even highly technical and abstract material, and I program very fast.
I'm pretty confident I'll be better than most experienced database
programmers in a short time. I learned all of Java and its class
libraries in about 2 weeks, without having ever done any object
oriented programming before. After that, I was better than just about
any Java programmer out there, apart of course from database
stuff. Now I'll add the database bit. Even if I end up hiring people to
do the work in the future, I need to have hands on experience before I
do so, so I know what's going on and can tell how skilled they are and
whether they're doing things correctly.

Wayne Busby <wayneb (AT) not (DOT) for.spam> wrote:

Unless you value your time extremely low, when you consider how
much time you will have to spend to write your own DBMS, OTS is far
cheaper.

Your system will almost certainly be less than what is already
out there. You will have more restrictions with what you can actually
accomplish. Do you really think that you can out-program the rest of
the world?

That can be a valid argument, but assuming that it is necessarily
true in your case is fallacious. Why do you need your own DBMS? Give
me five reasons. If they are anything like the arguments I am
responding to now, they will not hold up. Computer systems are a
means, not an end. If you want to get caught up in all the detail,
well, your argument applies to the operating system and the hardware,
too. Go ahead: design and build your own. Will you ever drain the
swamp?

You are extremely unlikely to get one from this. You will be
spending a lot of time writing something that is already rather a lot
a commodity. Your competitors will simply use off-the-shelf software
nearly every time and spend the time they have saved on their
respective competitive advantages.

And "may not"? You mean you do not already know? Find out
before you waste a lot of time writing something that will do even
less.

DBMSs are one thing to use, quite another to write.

I think you are deluded.

Your original post betrays basic ignorance about databases. At
this point, I doubt you really know what you do not know.

I really, really doubt it.

Maybe. What is quite likely is that you will get some ideas --
read as "biases" -- and will pass on anyone who does not share them.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

"Wayne Busby" <wayneb (AT) not (DOT) for.spam> wrote

Search the web for "databaseanswers". You'll find a website with over 200
models in it. Onwe of them, maybe more, deals with orders and
order-line-items.