Designing a small sports store database

[dbproject2]

Hello everyone,

I was just looking for some feedback on my progress on a small project i took up for designing a database for my own little sports store. right now its just a prototype and was wondering how well im doing up until now. Any help would be appreaciated. I'm using MS Access for the visual aspect of the database hoping that it helps. I also built diagrams to solidify the structure. Right now im not too sure my design will hold up so im looking for maybe some feedback on how well the design is. Thanks for your time and i hope im on track!

[mshmyob]

Can't read thumbnails. You could just attach your database and also tell us all your business rules.

Just showing us the diagram we cannot assume you have all your business rules correct.

cheers,

[orangeCat]

Agree with mshmyob - the thumbnails are too small to read.

Here is a site that has a variety of data models. I've pointed to a typical model of a "store". You can browse the site and see if there is something more suited to your needs.
** Edit ** Link to competing site removed as per site rules
If you follow links at the site, you'll see a tutorial.
Also, there is a link to a video describing the modeling process.

Hope this is helpful.

[NeoPa]

OrangeCat, I'm sure your motives are the purest as your other posts all seem helpful, but our rules (A link is available on every page under Help) preclude linking to other competing sites. As I say, I very much doubt you were leeching, but please be careful with your links.

[orangeCat]

NeoPa,
Thanks for the update. I was only trying to help.

I must say, though, that I did not consider the site as competing with Bytes.

I searched for " free online data models" and that was the first site. I have referenced that site for 5+ years -- only for base templates and rules for some models.

[NeoPa]

@orangeCat
I'm convinced you did not :)

As a general rule of thumb, a site is acceptable if it is provided by the same people who provide the software or package under discussion. Otherwise, if third parties provide forums where questions may be asked, then we see that as competition.

It's unfortunately true that there are some less scrupulous posters who will take any opportunity to leech traffic away to their own sites. Hence the rule we have to outlaw that behaviour.

Typically, we bear no ill-will towards these other sites. Quite the contrary. They are often kindred spirits of one type or another. We merely seek to protect our traffic from being diverted away. I hope that's clarified the position somewhat.

Welcome to Bytes!

[dbproject2]

Sorry about the small image. it seems this site has shrunk it. So im going to send the actual access database file. As for the business rules. here they are.

The database is meant to keep track of store transactions. There are 3 methods in which a customer can purchase an item. In store, online and through catalog. The database also holds information on payment method (credit card, gift card, cash). Another use of the database is to hold information of a customer who is a member of the membership plan who receives a points card. The database can also keep track of our suppliers and our products. It also holds employee information.

[orangeCat]

I don't have Acc2007.

[NeoPa]

In case it helps, here is a copy of some instructions I often post to help people attach a database and encourage people to work on it. Bear in mind the quoted text is general purpose and not written specifically for this instance. I hope it helps anyway.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database.
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[topher23]

@OrangeCat: Regarding the illegal link, it might be helpful if you could post a direct link to that video that you mentioned, particularly if it is hosted on YouTube as most videos these days are. You wouldn't be linking to a competing site at that point, just to a helpful video.

@dbproject2: I'm not too proud to own a copy of Access 2007. I'll take a look at it tonight if I get a chance.

Normally I don't accept downloads from strangers, even on forums, so be warned; if I get infected with anything by downloading your file, I will track you down and destroy you.

[mshmyob]

Hello DB,

I took a quick look and have a few points to make.

First let me say I could just give you the answer for your design but I would prefer you give it a try on your own so you learn better.

1. Read the following link about normalization.

2. You should go into the database tools/relationship view and setup your relations.

3. After reading about normalization sit down and write out all business rules , assumptions, and constraints for your design.

4. The minimum business rules you need for each set of joined (or related) tables is two. I will give you an example. You state that you want to track suppliers and products, yet in your design they are not even related. I would assume they should be and you would have seen this error if you had all your rules setup in advance. So here is an example of how you would relate them and the rules would determine the relationship type.

First Scenario
Each supplier can supply many products
Each product can be supplied by many suppliers

Note that each set of linked tables has a bidirectional set of rules (that is why I said two). This bi-directional set of rules determines the relationship type. In this case you have a Many to Many relationship and will therefore need a bridge table between the two table.

Second Scenario
Each supplier can supply many products
Each product is supplied by one supplier

These set of rules create a One to Many relationship and therefore do not need the bridge table.

As you can see you have two completely different designs (and that is only two tables) just based on a slight change in the business rules. Until you have a clear set of rules laid down you and all of us helping will just be spinning our wheels.

Please give it another try after reading the article on normalization and I will be happy to review your new design and give a more detailed answer on a proper design.

cheers,