General discussion

Locked

Database structure design

By andy ·
When I have designed databases before, I have used a field within the parent / child tables for the relation. Speaking to someone recently it has been suggested that a cleaner / better way of achieving the same goal is to use link tables.

e.g.
My method
=========
Purchase order parent table
Order number (Used to link to the child table)
Supplier name
Delivery date
etc.

Purchase order child table
Order number (Used to link to the parent table)
Item number
item description
etc.
Alternative method
==================
Purchase order parent table
Order number (Used to link to the link table)
Supplier name
Delivery date
etc.

Link table
Order number (Used to link to the parent table)
Item number (Used to link to thechild table)

Purchase order child table
Item number (Used to link to the link table)
item description
etc.

The reason given that makes the alternative method better is that records can be entered into either table independently.

I can only see this being an advantage if the integrity of the database is lost.

Should I continue with the method I have used previously, or should I get my head round this alternative method?

Are there any another gains to the alternative method?

Thanks in advance for any thoughts on this matter.

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Database structure design

by john_wills In reply to Database structure design

I think you should continue with your present method. Concretely, a purchase order has a quantity for each item, and the method suggested to you does not seem to take much account of that. How I would redesign what you have before you is have an item/thing table for products and let the key of each entry in the p.o. be the p.o. key augmented with a serial number, probably only a byte wide. But this may be what you have anyway.

The technique that has been recommended to you is suitable for a many-many-relationship, e.g. a lot of clinics and a lot of languages: each clinic can treat patients with a number of languages and each language may be used in several clinics. Your p.o. has a number of items or entries, but each entry has only one p.o. and each entry refers to only one kind of merchandise.

Collapse -

Database structure design

by andy In reply to Database structure design

Poster rated this answer

Collapse -

Database structure design

by MadestroITSolutions In reply to Database structure design

well, its not entirely clear, but what I can tell you is this:

Ideally, you should have a table for purchase orders, AND a table for Items. You use the link table to match items from your item table with purchase orders created. The reason to do it this way is to avoid data redundancy. If you continue doing it your way (as I understand, again, its not very clear), you will record the whole item information on the child table EVERY time you create a purchase order. It might not be a problem for 100 records, but after some time, you will run out of space. If you make an item table, then you simply use the link table, and enter the Item ID only. That way, you reduce data redundancy, and even increase speed, by creating lookups so that people can just select an item by ID, and the system will pull up information from the item table and fill up everything for them.

Hope that helps, if you have any other question, dont hesitate to drop a line to: juanja01@optonline.net

Good Luck!

Collapse -

Database structure design

by andy In reply to Database structure design

Poster rated this answer

Collapse -

Database structure design

by Bob Sellman In reply to Database structure design

Answer 2 points you in the right direction. I would suggest you have the following tables:

1. Inventory table (list of item data: item code, description, price(s), etc.)

2. Customer table: Customer ID, name, address, phone, contact, terms,credit rating, etc.

3. PO Header table: PO#, date, customer ID, terms for this PO, delivery address, etc.

4. PO Details: PO Line #, Item code, description of item (see note below), price each, quantity, etc.

5. PO Payments: PO#, date,amount, how paid, etc.


The description of item in #4 would normally be read from the item table, but you could allow modifications so that non-inventory items could be entered and also so that any changes needed to the description for that customer could be made. If you strictly limit items to those in your inventory table, then you could eliminate this field.

This avoids any many-to-many table relationships, which are bad design and provide an effective and efficient design. Even more important, it can help reduce data entry errors and speed data entry. For example, the description of an item is read from the item table, not retyped every time.

Note that I have also included a table to record payments for a purchase order. By using a separate (but related) table, multiple payments for the same PO can easily be recorded. Ideally, all POs are paid in full for the exact amount due, but whose customers all fall in the "ideal" category?

By the way, I think that the linktable is actually a very poor design. The PO header and the PO details (line items) tables ARE directly related by a common field, the PO#. Creating a third linking table just makes the database convoluted with no real benefit.

Collapse -

Database structure design

by andy In reply to Database structure design

Poster rated this answer

Collapse -

Database structure design

by chris loyens In reply to Database structure design

Whether it is necessary to use linktables or not is depending on the kind of relation you want to implement. The use of a linktable is only needed in case of many to many relations.
e.g. You've got a clienttable and a producttable. One client can order several products and a product can be ordered by a number of clients. In this case the relation between the entity client and the entity product is a many to many relation. To register an order you need a link table. The tables product and client need to have a unique id: the primary key(PK). In the ordertable (the link table) you define one foreign key (FK) for the clienttable and another one for the producttable:
client : PK_client, clientname, ...
product : PK_Product, productname, ...
order : PK_order, date, FK_client, FK_product, ...

In the link table there is a record for each product a client orders.

Regards

Collapse -

Database structure design

by andy In reply to Database structure design

Poster rated this answer

Collapse -

Database structure design

by andy In reply to Database structure design

This question was closed by the author

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums