Structure question...

[falled]

Hello people,

I want to build a simple Database, I've got Clients, Providers, articles and users.

A Client can order products, But I don't know how to store the content of this orders. Yes, I know I could create a table with all the products again with all the orders

ID article qt order
1 1 3 323
....

There's any other way to do this? to store the orders separately? This way creates a very large table, I'd like to create something Like little databases for each order (Yeah I know this sounds a little stupid), But what about store it in a little XML for each order?

What do you think?

Please help me


Thank you for your patience and sorry for my bad writting
I hope you have understood my idea.

 

[PHV]

Have a look at the sample Northwind database provided mith access.
Anyway, good starting point here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[falled]

Yes,

I've looked at the example of Northwind and I saw that for the orders it builds a large database of articles as I said before, I'm wrong?

 

[lespaul]

The Northwind database doesn't handle orders/inventory/transaction like it really should. Check out Thread181-1317102 for a discussion on inventory.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Golom]

Access isn't an industrial strength RDBMS like Oracle or SQL Server but it is still capable of handling an impressive amount of information. Unless you are into the environment where you have hundreds of orders per day, each containing hundreds to thousands of line items, Access can probably handle it ... at least for a while.

As to the other ideas ...

I guess you could resort to multiple databases or something like XML. That would sort of work for saving the data but would be an absolute nightmare for retrieval, editing and reporting. In essence, you would need to build all the infrastructure that the JET engine provides because the tools available in JET pretty much assume that you have your data in a database ... not scattered in files all over the place.

 

[JoeAtWork]

This way creates a very large table

And the problem with this is?

Either way you are storing the same amount of data. The method with the "very large table" is a relational database. It can be queried and summarized in all sorts of ways (think reporting). It can (and should be) designed to enforce data integrity. It's all one file, therefore one backup, and orders will not likely be lost by "putting them in the wrong folder". You can have hundreds of thousands of records and still bring up a particular order in a second (how long do you think it would take you to pick out the correct XML file if you have thousands of them?).

Your method sounds like a file system, i.e. you could do the same just by creating a bunch of Excel spreadsheets. I see no advantage to that, and a nightmare in maintenance. The usual evolution that businesses go through is from using spreadsheets to relational databases.

I think you are making a technical decision based on "gut feeling". If "mini-databases" worked better than relational ones, that's what all the big guys would be using.

 

[falled]

I'm afraid of Access search speed decrease a lot.

And yes, I realize that XML searching will it will take more time, I was wrong and maybe the best way is a large table or two

Thank you all for your time

Be happy