DB Structure Question - Tell me your suggestions!

[Delta Tango]

Hey Everyone,

I am creating a database that has 4 tables:

customers

orders

orderitems

catalog_items

A customer will log into this site and ONLY the catalog items that have been applied to that customer's account can be listed.

In other words, let's say there are 400 catalog_items but that customer only can sell 90 of them, that is all that will appear.

I am debating what is the best way to do this.

I could create a table called allowed_items that is the same as catalog_items but with an extra field that says who this is approved for, but that table will be huge, and there will be duplicates (if 5 customers sell the same product there will be 5 records).

The other solution I came up with is to create a blob field in the customers table called allowed_items that just put a table or comma delineated list of all the items.

I could also create a blob field in the catalog_items and list the users who are allowed.

Does this seem reasonable?

Suggestions are appreciated.

Dave

[bcooney]

Join table between customers and catalog_items?

[Cannonball]

As far as good database design, you need another table. Any time you identify a many-to-many relationship, you should strongly consider adding a table. This table may be long, but it only needs two feilds:

customer_id

item_number

Then, your layouts (or scripts) that show products to customers when they log in would iterate through the 'join table' (maybe called "approved_items") for any lines that match the customer_id.

Now, I am speaking from the world of relational database design, not FileMaker Pro design. I would hope they are the same, but I am very new to FileMaker. Good Luck!

Edited October 13, 2007 by Guest
Clarity

[Hazelhorst]

I agree that creating a separate table is the best solution from a database design view. In FMP however, it is possible to enter multiple key values in one field, each on its own line. If your only purpose is to limit the choice of items (and the itemlist is limited anyway), this may be a very practical solution.

That said, using an extra table also gives you extra options, such as adding a validity (expiration date), that you can include in the relation definition. It depends on what your needs are.