Help - Structuring my Database (1 Viewer)

[smaviddavid]

Hello, firstly apologies if this is the wrong section, and could a mod please relocate it the appropriate one.

I am currently building a database for my girlfriends parents who run a jewelry business. I have had no problem up until now when i saw my table becoming very long, i ignored this at first as they would only be able to edit it via the form when complete.

However.. i googled and i saw that ideally i should build my tables 'down' and not 'across' So i've hit a wall.

The contents of my current database consists of this;

BOLD items signify there is 2 sets of this data, one for him, one for her.

tblclient

• ID (primary key)
• raised by (dropdown box from tblemployee)
• date rasied
• first name
• surname
• address line 1
• address line 2
• town
• county
• post code
• telephone
• email him
• ring details
• ring size (dropdown box from tbl size)
• ring material (dropdown box from tblmaterial)
• ring location (dropdown box from tbllocation)
• ring holder (dropdown box from tblemployee)
• delivery date
• deadline
• paid
• payment details

tbl employee

• employee

tbllocation

• locations

tblemployee

• employee

So the main question is.. is there a better way to structure this? I can provide an empty database for people if needed.

 

[Keiath]

The only thing I would do is have 1 database for the customer details

then use a subform for the orders

This eay you will have a 'contacts' database good for repeat business. and the orders in one place

Hope that helps

 

[smaviddavid]

Well in that case i shall carry on with my current structure just having 20+ fields seem silly if there was a better way.

As for repeated orders, these are custom made wedding/engagement rings, so fingers crossed they shouldn't be coming back for new ones unless they loose them! lol. If so they will be key'ed on as a new ring to track. As it wont be 2 rings, it'll be one.

Thankyou for your fast reply.

 

[spikepl]

Google normalization

Your table structure is not good, it implies one trade per customer and that's it. Further, making lookup fields in a table is not recommendend. You can alway assemble info from multiple related tables using a query.

 

[smaviddavid]

Well i did google it and its why i ended up here! lol, its very confusing, but i am still looking into it, however my relationships etc never worked! Also in theory it should be 1 trade per customer, only in rare cases would a person need to come back for another ring. If so they would be key'ed on as a new client for following reasons;

New spouse (meaning the customer info would change as spouses name would vary)
Remake/fix of ring.

Thinking about it, each client (a couple..) would be having 2 rings.. so it would be classed as 2 orders and in one case i think the husband got 2 rings.. so i will look into setting up a subform for orders. How would i go about this? i can do it in a week of searching, but a helping hand would be much appreciated.



Feel like a little kid here giving out my msn, but if anybody can help add me on msn smaviddavid@ hotmail.co.uk

 

[smaviddavid]

Sorry if double posting is against rules, just felt editing would make my previous messy post messier!

After thinking, how does this setup sound


CLIENT INFORMATION
-->order history (subform)
---->Order tracking (subform of a subform)



Client information

• id
• his first
• his sur
• her first
• her sur
• address line 1
• address line 2
• town
• county
• post code
• his contact
• her contact

order history

• his/her (who the ring is for)
• material
• description
• cost
• date added
• date required
• notes on ring
• ring status
• delivered.

order tracking

• transaction (given/returned)
• person/place
• date

As the main principle for this database is to track the rings through their progress, so if a client were to ring up then they would know the location of the ring whether its in the safe, workshop, setters, or if its at the reps house for delivery. So would the setup be acceptable?


---------------------
Edit..

I have this now setup in a table format with relationships, and it works in the table perfectly.. now trying to incorpate it into a form. Then i shall split it. Although having troubles, i seen what i want it do to in the form from a template. However doing so is harder